+ Reply to Thread
Results 1 to 6 of 6

Text to Column with entries on separate lines

  1. #1
    Registered User
    Join Date
    04-12-2014
    Location
    Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    3

    Text to Column with entries on separate lines

    Hi everyone,

    My problem right now is that I have to split a cell into different columns but it's not separated by any character or any tabs. The different entries in the cell are written on different lines in the cell (like when pressing Alt+Enter to write on another line in the same cell). The cell looks like this:

    AAA
    BBB
    CCC
    DDD

    I would like to be able to separate the entries into different column. How can I do this?

    If I try the Text to Column function, I am only able to get the first entry, and it deletes the rest.

    Thank you for your answers.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Text to Column with entries on separate lines

    Hi,

    =TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",255)),255*(COLUMNS($A:A)-1)+1,255))

    Copy to the right as required.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    04-12-2014
    Location
    Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Text to Column with entries on separate lines

    Solved

    If anyone is wondering, when using Text to Columns, select Delimiter as Other and type in Alt+0010 .

    Now, how can I put then in different Rows?

    Quote Originally Posted by XOR LX View Post
    Hi,

    =TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",255)),255*(COLUMNS($A:A)-1)+1,255))

    Copy to the right as required.

    Regards
    Thanks for the answer, problem with using TRIM is that the data I have is more like:

    AAA XXX L.L.P.
    BBB YYY L.L.P, something
    etc

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Text to Column with entries on separate lines

    Try this formula in B1

    and drag formula across

    =MID(SUBSTITUTE($A1,CHAR(10),""),COLUMNS($A$1:A1),1)

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    AAA
    BBB
    CCC
    DDD
    A A A B B B C C C D D D
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Text to Column with entries on separate lines

    I don't see why TRIM is a problem there?

    Perhaps you should upload an actual workbook with some examples and your expected result in each case.

    Regards

  6. #6
    Registered User
    Join Date
    04-12-2014
    Location
    Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Text to Column with entries on separate lines

    Quote Originally Posted by XOR LX View Post
    I don't see why TRIM is a problem there?

    Perhaps you should upload an actual workbook with some examples and your expected result in each case.

    Regards
    Tried it your way and it works. Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 11-19-2012, 05:25 AM
  2. excel vba to separate single column entries into database format
    By antaga082 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-14-2012, 11:33 PM
  3. Is it possible to separate and count lines in a column
    By David2011 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-02-2011, 07:28 PM
  4. Extract existing IDs of entries repeated in a worksheet in a separate column
    By harsh2209 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-07-2011, 07:56 AM
  5. [SOLVED] Multic Column List Box - Can I add vertical lines to separate colu
    By Mark S. in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2005, 06:06 PM

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