Replace commas with tabs

Is there any way I can use Find and Replace (or anything else) to replace commas with tabs? I want to use this on a data download (text file) of stock prices which looks like this:

AAC,20061221,1.84,1.85,1.83,1.85,82487
AAE,20061221,0.38,0.39,0.37,0.38,325573
AAI,20061221,0.00,0.00,0.00,0.00,0
AAM,20061221,0.21,0.21,0.20,0.20,241057
etc.

Will be grateful for any help.
Bungaree

I can't guarantee this will work for you, but try this for those cells where you want the comma changed to tabs. It will probably look weird on the screen, but open it as a text file to make sure:

=SUBSTITUTE(A1,",",CHAR(9))

Scott

Originally Posted by bungaree

Is there any way I can use Find and Replace (or anything else) to replace commas with tabs? I want to use this on a data download (text file) of stock prices which looks like this:

AAC,20061221,1.84,1.85,1.83,1.85,82487
AAE,20061221,0.38,0.39,0.37,0.38,325573
AAI,20061221,0.00,0.00,0.00,0.00,0
AAM,20061221,0.21,0.21,0.20,0.20,241057
etc.

Will be grateful for any help.
Bungaree

I am assuming that you wnt to split these values into the cells so that you can easily format your data...If I am right...then here you go:

Select the Column/Cell which you want to split.

then go to Data-->Text to Column--> Select Delimited as option-->Click next--> Check the box left to 'Comma' option and click Finish.

It will split the data into different cols whereever it will find ','.

Hope this helps!!!

Scott and Vikas, such quick replies!

Vikas, no, I didn't want to split to columns, I wanted to retain the data in 1 column, but thank you for taking the time.

Scott, thank you for your suggestion - I'll keep that formula on reference. It does the trick in this case, but the program I'm using to apply this data to my chart program is reading the data with quotes at the beginning and end of each string. The quotes are not visible in the txt file. Any way I can get rid of these?

(My supplier omitted to send me Friday's data and I've spent quite some time trying to modify other data to apply to my chart program)

Much appreciated!
Bungaree

BINGO!! Scott and Vikas - problem solved thank you so much!! This way also allowed me to format the date the way it should be which was another problem I was having. I only wish I'd found this forum and posted on Saturday.

Thank you!
All the best in the new year,
Bungaree

Originally Posted by bungaree

BINGO!! Scott and Vikas - problem solved thank you so much!! This way also allowed me to format the date the way it should be which was another problem I was having. I only wish I'd found this forum and posted on Saturday.

Thank you!
All the best in the new year,
Bungaree

Glad your problem is solved...Thnx for the response... and undoubtedly :

Cheers To Scott

i have the same request. stock data separated by commas. i was able to
seperate one file but most of the time it says cells are merged and cant do it.
when i try to unmerge all the data is lost except left most.

tried to open the file with the import data command that didnt work either.