Soumik Dutta
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.
- 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.
- You will see a comma replace every line break of the strings.
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
- How to Put Multiple Lines in Excel Cell (2 Easy Ways)
- VBA to Generate Multiple Lines in Email Body in Excel (2 Methods)
- How to Add a Line in Excel Cell (5 Easy Methods)
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.
- Now, select the entire range of cells C5:C9.
- Go to the Home tab.
- Now, select Editing > Find & Select > Replace.
- 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.
- You will see every line break will replace with a comma.
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.
- A dialog box will appear.
- Now, in the Insert tab on that box, click Module.
- 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.
- Now, select the entire range of cells C5:C9.
- 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.
- At last, you will see that in the position of every line break a comma has come.
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!
Very nice and usable the Substitute Function as well as the VBA Code which you have developed.
Thanks.