+ Reply to Thread
Results 1 to 8 of 8

Replace commas with tabs

  1. #1
    Registered User
    Join Date
    12-26-2006
    Posts
    78

    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

  2. #2
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581
    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

  3. #3
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303
    Quote 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!!!

  4. #4
    Registered User
    Join Date
    12-26-2006
    Posts
    78

    Replace commas with tabs

    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

  5. #5
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581
    Are you talking about when you save it from Excel as a CSV or TXT file, it puts quotations around the line?

    If so, you could probably just go in with Notepad and replace " with [blank].

    ________

    [Edit: If I understood you correctly, this second method is probably better]


    Thinking about this again, you can use Vikas' suggestion of splitting the cell up followed by saving it according to the description below. (Using text to columns, using the [comma] as the separator)

    After having done this, the values should each be in distinct cells. Then, Save the file, specifying the Save as Type: Text (Tab delimited) (*.txt). (The result is the value in each cell is separated by a tab)

    It will probably do what you want with less hassle.

    Scott
    Last edited by Maistrye; 12-26-2006 at 03:14 AM.

  6. #6
    Registered User
    Join Date
    12-26-2006
    Posts
    78
    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

  7. #7
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303
    Quote 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

  8. #8
    Registered User
    Join Date
    01-02-2007
    Posts
    1
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1