Results 1 to 7 of 7

Delimiting data in Excel

This is a discussion on Delimiting data in Excel within the Excel Questions forums, part of the Question Forums category; I have been given some data in an excel spreadsheet which looks like it has just been pasted from a ...

  1. #1

    Join Date
    Jul 2005
    Location
    Brisbane, Queensland, Australia
    Posts
    57

    Default Delimiting data in Excel

    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

  2. #2
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,134

    Default

    Sure,

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

    HTH,

    Smitty
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  3. #3

    Join Date
    Jul 2005
    Location
    Brisbane, Queensland, Australia
    Posts
    57

    Default

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

    Thanks again
    Jac

  4. #4
    Board Regular brian.wethington's Avatar
    Join Date
    Jul 2006
    Location
    Irving TX
    Posts
    1,721

    Default

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

    Start by doing what's necessary; then do what's possible; and suddenly you are doing the impossible.
    St. Francis of Assisi

  5. #5
    Board Regular MS_Xsmell's Avatar
    Join Date
    Jan 2007
    Posts
    51

    Default

    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.

  6. #6
    Board Regular MS_Xsmell's Avatar
    Join Date
    Jan 2007
    Posts
    51

    Default

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

  7. #7

    Join Date
    Jul 2005
    Location
    Brisbane, Queensland, Australia
    Posts
    57

    Default

    Thank you Brian.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com