Separate data string into numeric and text

[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

 

[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.

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