Share via

I have a cell that has multiple lines of data in it. I need each line to become a new column. How do I do this?

Anonymous
2022-10-17T16:39:23+00:00
Example of the data in the cell. there are returns after each line<br><br> <br><br><br>W/ S/L <br> <br><br><br>Initial -  AC <br> <br>Card # NA <br> <br>Lot #  NA <br> <br>Ref WO# HVC 235065 <br> <br>Material:  405 <br> <br>Heat #  MM41T0RR17 <br> <br>Raw Stk Loc.  NA <br> <br>Tag #  NA <br> <br>Diameter:  NA <br> <br>Head Marking: " NICU 50929" <br> <br>Test / Date: L1: LP  7-23-20<br><br> <br><br><br>I need the data to now be expanded by 12 columns<br><br><br> W/ S/L Initial- AC Card# NA Lot # NA Ref WO# HVC 235065 Material: 405 Heat# MM41T0RR17 Raw Stk Loc. NA Tag# NA Diameter: NA Headmarking: " NICU 50929" test / Date:L1 LP 7-23-20 <br> --- --- --- --- --- --- --- --- --- --- --- --- <br><br><br> <br>Thank you
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2022-10-17T23:33:50+00:00

    Hi,

    In the second screen of the Text to Columns window, check the box of Other and type Ctrl+J in the box. See image below

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-10-17T20:53:00+00:00

    Was this answer helpful?

    0 comments
  3. Anonymous
    2022-10-17T19:58:46+00:00

    How are you with macros? Make sure there are blank columns to the right of those cell, then select your cells and run this macro:

    Sub SplitCells() 

    Selection.TextToColumns Destination:=Selection.Cells(1, 1), DataType:=xlDelimited, \_ 

    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, \_ 

    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar \_ 

    :=Chr(10)

    End Sub

    Was this answer helpful?

    0 comments
  4. Anonymous
    2022-10-17T19:04:34+00:00

    Hi Bernie, Thank you for the help. If I hold down the control key I am not able to type anything into the "other" box. If I do not hold down the control key I can type in a single digit. Right now I only get one new column.

    Andrea

    Was this answer helpful?

    0 comments
  5. Anonymous
    2022-10-17T16:45:14+00:00

    Select your cells and use Data / Text to columns / Delimited / Next> and in the dilog's Other box hold down the Ctrl key and type 0010 on the number keypad. Check "treat consecutive delimiters as one" and press OK.

    Was this answer helpful?

    0 comments