Excel: How to Replace Commas with Line Breaks


You can use the following two-step process to replace commas with line breaks in Excel:

Step 1: Use SUBSTITUTE Formula

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

Step 2: Turn on Wrap Text

The following example shows how to replace commas with line breaks in Excel in practice.

Example: Replace Commas with Line Breaks in Excel

Suppose we have the following column in Excel that contains the team name, position and points scored by 10 different basketball players:

Suppose we would like to replace the commas in each cell with line breaks instead.

To do so, we can type the following formula into cell B2:

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

We can then click and drag this formula down to each remaining cell in column B:

Next, highlight the cell range B2:B11 and then click the Wrap Text icon in the Alignment group on the Home tab:

The cells in the range B2:B11 will be formatted with line breaks:

Excel replace commas with line breaks

Note that the Wrap Text feature in Excel is used to display all of the text in a given cell without letting it overflow to other cells.

We used the SUBSTITUTE function to replace commas with line breaks, but in order to visualize these line breaks we had to turn on Wrap Text so that Excel displayed the text in each cell with multiple lines.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Replace #N/A Values in Excel
How to Replace Blank Cells with Zero in Excel
How to Search for Special Characters in a Cell in Excel

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *