Delimiting data in Excel

[AussieJac]

I have been given some data in an excel spreadsheet which looks like it has just been pasted from a txt file as it has 800 rows of data but all the data is contained in column A separated by commas (,) & the squiggly thingy (~).

Example

Biell~Alfre ~18/04/1907~1395 Biell,Alfre Lionel,(18/04/1906),(09/03/2001),Nazae,Rediffe,420

They cannot supply me with the original txt file so I can't use the delimiter function.
I need to separate this one line of data currenly contained in column A into separte columns across the spreadsheet so I can work with the data.

Is there a way to tell excel to copy the characters between the (,) & (~) into separate columns?

Many thanks
Jac

 

[Smitty]

Sure,

Data-->Text to Columns-->Delimited-->Check Space & Other-->~

HTH,

Smitty

 

[AussieJac]

Thank you.
That did the trick.
I knew there would be a quickie fix!

Thanks again
Jac

 

[brian.wethington]

the squiggly thingy (~).

Just for future reference, not trying to be a jerk, it is a tilde (easier to type than squiggly thingy).

Brian is now done hijacking the thread.

 

[MS_Xsmell]

Try openning it in excel. Save it as a .txt file.

File>Open>(change file type to all types)>Select the file and instead of clicking open, click the little arrow next to it... select 'Open and Repair'>Repair>Delimited... etc etc

Hope that helps.

 

[MS_Xsmell]

Pennysaver... there is a reason you're an MVP and I'm a regular.

 

[AussieJac]

Thank you Brian.