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:
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: