When we copy some text from somewhere to our Excel spreadsheet, sometimes some line break is trapped in that 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 context, 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 in familiarizing yourself with the amazing features of Excel, download our practice workbook and follow this article.
How to Replace Line Break with Comma in Excel: 3 Easy Methods
To explain 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 Replace a Character with a Line Break in Excel
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 be replaced with a comma.
Thus, we can say that our method has worked effectively.
Read More: Find and Replace Line Breaks in Excel
3. Embedding VBA Code
Writing a VBA code can also help you 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. You can also press ‘Alt+F11’ to open 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 can replace every line break with a comma in an Excel spreadsheet.
Read More: How to Make Excel Go to Next Line Automatically
💬Â 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.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
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.
Keep learning new methods and keep growing!
Related Articles
- How to Space Down in Excel
- [Fixed!] Line Break in Cell Not Working in Excel
- How to Remove Line Breaks in Excel
- How to Do a Line Break in Excel
<< Go Back to Line Break in Excel | Text Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Very nice and usable the Substitute Function as well as the VBA Code which you have developed.
Thanks.