Separate data string into numeric and text

  • Thread starter Vanessa Cassidy
  • Start date
V

Vanessa Cassidy

Hi

Can anyone help me with a script, to read in a string of mixed
numerical and text data, and separate them to a specific format of
spacing.

This is the raw data:

11-11-11,44444444,JOE R BLOGGS,2757.42,
I need to convert the data into
11111144444444JOE R BLOGGS 2757.42

Firstly i need to remove the "-" and ",", so that it all joins up.
There needs to be 6 chars at the start for 111111.
At the 7th char i need to leave space for 8 digits (44444444 -
although sometimes the no. being read in will be less than this).
Then 15 chars must be left for name, and the amount will be printed
after that.

Any ideas on what application would do, and how i would best to go
about this ?

Thanks
 
J

Jan Karel Pieterse

Hi Vanessa,
11-11-11,44444444,JOE R BLOGGS,2757.42,
I need to convert the data into
11111144444444JOE R BLOGGS 2757.42

Firstly i need to remove the "-" and ",", so that it all joins up.
There needs to be 6 chars at the start for 111111.
At the 7th char i need to leave space for 8 digits (44444444 -
although sometimes the no. being read in will be less than this).
Then 15 chars must be left for name, and the amount will be printed
after that.
Click to expand...

You might first do a search and replace, searching for the dashes and
replacing them with nothing.
Then select the data, choose "Data, text to columns" from the menu,
choose delimited and check the comma delimiter.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Top