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

When we copy some text from somewhere to our Excel spreadsheet, sometimes some line break is trapped in those data. Removing this line break is quite an easy job to do. Sometimes, it becomes required to replace the line break with some other character like a hyphen, dash, comma, or something else. In this content, we will demonstrate to you 3 different procedures on how to replace every line break with a comma in your Excel worksheet. If you are interested to familiarize yourself with the amazing feature of Excel, download our practice workbook and follow this article.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


3 Easy Methods to Replace Line Break with Comma in Excel

For explaining the approaches, we consider a dataset of 5 strings. In these strings fruit’s name, its quantity, the delivery location, and the delivery state of these fruits are mentioned. The datasheet in the ranges of cells B5:B9. 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.


1. Using SUBSTITUTE Function to Replace Line Break

In this approach, we will use the SUBSTITUTE Function to replace the link break with a comma. Our dataset is in the range of cells B5:B9. The output will be in the range of cells C5:C9. The steps of this method are given as follows:

📌  Steps:

  • First, select cell C5.

  • Now, write down the following formula in cell C5.

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

  • Press the Enter key on your keyboard. You will see that in the position of every line break a comma has come.

Using SUBSTITUTE Function to Replace Line Break with Comma

  • Then, double-click on the Fill Handle icon with your mouse to copy the formula up to cell C9. Or you can simply 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

In the end, we can say that our process and formula worked successfully.

Read More: How to Do a Line Break in Excel (4 Ways)


Similar Readings


2. Replace Line Break with Comma Through ‘Find and Replace’ Command

In this method, we are going to use the Excel built-in feature Find and Replace command to replace the line break with a comma. We will use the same dataset which has already been used in our previous process. The dataset is in the range of cells B5:B9 and the result will be in the range of cells C5:C9. The steps of this process are given below:

📌 Steps:

  • First of all, select the entire range of cells B5:B9.
  • Press ‘Ctrl+C’ on your keyboard to copy the dataset.

  • Then, press ‘Ctrl+V’ to paste the data into the range of cells C5:C9.

Replace Line Break with Comma Through Find and Replace Command

  • Now, select the entire range of cells C5:C9.
  • Go to the Home tab.
  • Now, 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 by mouse and press ‘Ctrl+J’.
  • Next, 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 replace with a comma.

Replace Line Break with Comma Through Find and Replace Command

Thus, we can say that our method has worked effectively.

Read More: Find and Replace Line Breaks in Excel (6 Examples)


3. Embedding VBA Code

Writing a VBA code can also help you to replace every line break with a comma in an Excel worksheet. Our dataset is in the range of cells B5:B9 and the result will be in the range of cells C5:C9. The process is explained below step by step:

📌 Steps:

  • To start the approach, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

Embedding VBA Code

  • A dialog box will appear.
  • Now, in the Insert tab on that box, click Module.

Embedding VBA Code

  • Then, write down 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.
  • After that, 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

  • Now, select the entire range of cells C5:C9.

Embedding VBA Code to Replace Line Break with Comma

  • Now, 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

  • At last, you will see that in the position of every line break a comma has come.

Embedding VBA Code to Replace Line Break with Comma

Finally, we can say that our visual code worked successfully and we are able to replace every line break with a comma in an Excel spreadsheet

Read More: Excel VBA: Create New Line in MsgBox (6 Examples)


💬  Things You Should Know

You can also modify the text in a manual process. In that case, you have to 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 the ‘, ’ button on your keyboard to insert it. Done!

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


Conclusion

That’s the end of this context. I hope that the article will be helpful for you and you will be able to replace every line break with a comma in Excel. If you have any further queries or recommendations, please share them with us in the comments section below.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

1 Comment
  1. Reply
    Malin Chandra Deka May 26, 2022 at 10:18 PM

    Very nice and usable the Substitute Function as well as the VBA Code which you have developed.

    Thanks.

Leave a reply

ExcelDemy
Logo