Spreadsheet Practice

How to replace line breaks with commas in Excel

Formula (Excel and Google Sheets)

=SUBSTITUTE(A2,CHAR(10),", ")

Use =SUBSTITUTE(A2,CHAR(10),", "). CHAR(10) is the line break inside a cell.

Result on the example
one result per row in B2, filled down to B6
Works in
Excel 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

How it works

Each line break is the character CHAR(10). SUBSTITUTE swaps every one of them for a comma and a space, so the lines become one list that is easy to paste into an email or another system.

=SUBSTITUTE(A2,CHAR(10),", ")

Result"chair, desk, lamp"

  1. A2"chair desk lamp"

    The value in A2.

  2. CHAR(10)" "

    CHAR: Character for a code number.

    • number10
  3. SUBSTITUTE(A2,CHAR(10),", ")"chair, desk, lamp"

    SUBSTITUTE: Replaces matching text.

    • textA2"chair desk lamp"
    • old_textCHAR(10)" "
    • new_text", "

In Google Sheets

The same formula works in Google Sheets: =SUBSTITUTE(A2,CHAR(10),", "). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=TEXTJOIN(", ",TRUE,TEXTSPLIT(A2,CHAR(10)))chair, desk, lamp, monitor, dock, cable, mouse, keyboard…Excel 365: split at the line breaks and join with commas. TRUE skips empty lines, which SUBSTITUTE would turn into ", ,".

Common mistakes

  • An empty line gives a double comma with SUBSTITUTE. Use the TEXTJOIN version, or TRIM the text first.
  • Some exports use CHAR(13) and CHAR(10) together. Replace CHAR(13)&CHAR(10) first, or you get stray invisible characters.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Replace line breaks with commas

In B2, turn the list with one item per line into a comma-separated list, and fill down to B6.

Questions

How do I go the other way, from commas to line breaks?

Swap them: =SUBSTITUTE(A2,", ",CHAR(10)), and turn on Wrap Text.

Does Google Sheets use the same character?

Yes, CHAR(10).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.