+ Reply to Thread
Results 1 to 4 of 4

Separating Text in One Cell into Multiple Field (Issues with Text to Columns)

  1. #1
    Registered User
    Join Date
    05-03-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    2

    Separating Text in One Cell into Multiple Field (Issues with Text to Columns)

    Hi,

    I received an excel sheet with about three hundred records. All the information is stored in one cell with three lines of text in each row. The first row contains the First & Last Name, Second Row is Telephone number, and third is Fax Number. When I used the Delimited Function it does not identify the second and third row of the cell, but just the first. When I use the fixed option it is impossible to include all the records without cutting off a name or part of a telephone number in the process.

    Is there a better way to separate text in cell (with three lines of text in each cell) into multiple fields? Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Separating Text in One Cell into Multiple Field (Issues with Text to Columns)

    Per your sample you will have a problem using text to columns and aligning the data correctly given the inconsistencies of the strings

    If you want to use Text to Columns though you can - first though

    Select the Data
    Launch the Edit -> Replace dialog
    In the Find What dialog hold ALT and type in 0010 on the numeric keypad (if using a laptop hold FN + ALT and use numeric keypad (ie normally letters))
    In the Replace With dialog enter a character otherwise not found in the strings - eg #
    Select Replace All

    Now run Text to Columns -> Delimited -> Other use # and click Finish.

    Else you're looking at VBA / Formulae to split the strings - let us know.

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Separating Text in One Cell into Multiple Field (Issues with Text to Columns)

    you can do that within text to columns itself
    select delimited
    uncheck everything
    choose other and in the box do alt+010
    as you release the alt key you'll see them split properly
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    05-03-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Separating Text in One Cell into Multiple Field (Issues with Text to Columns)

    Thank You very much this was very helpful!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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