How to Replace a Line Break with Comma in Excel (3 ways)

We have a dataset of 5 strings. In these strings, the fruit’s name, its quantity, the delivery location, and the delivery state of these fruits are mentioned. In every piece of information, there is a line break. We will replace every line break with a comma. After completing the task, the final result will be in the range of cells C5:C9.

Method 1 – Using the SUBSTITUTE Function to Replace Line Break

Steps:

Select cell C5.

Enter the following formula in cell C5:

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

Press Enter. You will see that in the position of every line break, a comma has appeared.

Double-click the Fill Handle icon with your mouse to copy the formula to cell C9. You can also drag the Fill Handle icon up to cell C9.

You will see a comma replace every line break of the strings.

Method 2 – Replacing a Line Break with a Comma Through ‘Find and Replace’ Command

Steps:

Select the entire range of cells B5:B9.
Press ‘Ctrl+C’ to copy the dataset.

Press ‘Ctrl+V’ to paste the data into the range of cells C5:C9.

Select the entire range of cells C5:C9.
Go to the Home tab.
Select Editing > Find & Select > Replace.

A dialog box entitled Find and Replace will appear.
In the empty box beside Find what, click on and press ‘Ctrl+J’.
In the Replace with option, type ‘, ‘ and click on Replace All.

You will see every line break will be replaced with a comma.

Method 3 – Embedding VBA Code

Steps:

Go to the Developer tab and click on Visual Basic. If you don’t have that, you must enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

A dialog box will appear.
In the Insert tab on that box, click Module.

Enter the following visual code in that empty editor box.

Sub Replace_Line_Breaks_with_Comma()
For Each Cell In Selection
Cell.Value = Replace(Cell.Value, Chr(10), ", ")
Next
End Sub

Close the Editor tab.
Select the entire range of cells B5:B9.
Drag the Fill Handle icon to the right side to copy the data into the range of cells C5:C9.

Select the entire range of cells C5:C9.

From the View tab, click on Macros > View Macros.

A new dialog box called Macro will appear. Select Replace_Line_Breaks_with_Comma.
Click on the Run button to run this code.

Finally, you will see that in the position of every line break, a comma has appeared.

Things You Should Know

You can also modify the text manually. In that case, double-click your mouse on your desired cell. Then, place the cursor at the beginning of the word of a line and press Backspace on your keyboard. The link break will go out. Now, press ‘, ’ to insert it. Done!

You can go through it if you have a very limited amount of data like us. However, if you have to handle a large datasheet, we recommend you choose the other approaches described above.

How to Replace a Line Break with Comma in Excel (3 ways)

Method 1 – Using the SUBSTITUTE Function to Replace Line Break

Method 2 – Replacing a Line Break with a Comma Through ‘Find and Replace’ Command

Method 3 – Embedding VBA Code

Things You Should Know

Related Articles

Leave a reply Cancel reply