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.

Using SUBSTITUTE Function to Replace Line Break with Comma

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.

Using SUBSTITUTE Function to Replace Line Break with Comma

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

Using SUBSTITUTE Function to Replace Line Break with Comma

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.

Replace Line Break with Comma Through Find and Replace Command

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

Replace Line Break with Comma Through Find and Replace Command

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.

Replace Line Break with Comma Through Find and Replace Command

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

Replace Line Break with Comma Through Find and Replace Command

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.

Embedding VBA Code

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

Embedding VBA Code

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.

Embedding VBA Code to Replace Line Break with Comma

Select the entire range of cells C5:C9.

Embedding VBA Code to Replace Line Break with Comma

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.

Embedding VBA Code to Replace Line Break with Comma

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

Embedding VBA Code to Replace Line Break with Comma

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