Share via

How can I parse fields in Excel with a carriage return as the field delimiter?

Anonymous
2012-03-30T18:23:42+00:00

I am working in Excel and have a series of field data which is delimited by a carriage return (new paragraph). Is there any way I can parse this data out into separate columns in the worksheet?

Microsoft 365 and Office | Excel | For home | 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

Answer accepted by question author

  1. Anonymous
    2012-03-30T18:38:04+00:00

    Jennie_Cultus,

    Not sure about doing this in a single hit but here's a 3 step procedure:

    1. Convert the carriage returns to commas with  =SUBSTITUTE(A1,CHAR(10),",")
    2. Copy the result of above and paste special Value into another cell
    3. Use Text to Columns (Data tab, Data Tools group)

    ___________

    Regards, Tom

    10 people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2012-03-30T19:59:32+00:00

    Jennie_Cultus,

    Try adding a Step 0. Use  =CLEAN(A1)

    Hopefully that will get rid of hidden chars, before the Substitute.

    ___________

    Regards, Tom

    7 people found this answer helpful.
    0 comments

16 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-03-30T18:59:27+00:00

    Not sure about doing this in a single hit but here's a 3 step procedure:

    1. Convert the carriage returns to commas with  =SUBSTITUTE(A1,CHAR(10),",")
    2. Copy the result of above and paste special Value into another cell
    3. Use Text to Columns (Data tab, Data Tools group)

    You can use Text To Columns directly without having to use that SUBSTITUTE formula. When the Step 2 page in the dialog box appears, uncheck all the delimiters and then put a check mark in the checkbox labeled "Other" then click into the empty field next to it and type ALT+0010 using only the Number Pad to enter the digits.... the Data Preview table should now show the paragraphs split across columns.

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2012-03-30T19:57:29+00:00

    Data/Text to Columns. In the Other field, type ctrl/j (you will see just a period, but it's the carriage return)

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2012-03-30T18:42:45+00:00

    Is it essential to parse this directly from the data file?  If you open the data file in Word you can use Find and Replace to replace the carriage return with a different character, such as Tab, this will then import into Excel across a single row.

    1 person found this answer helpful.
    0 comments