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

Get FREE Advanced Excel Exercises with Solutions!

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.

### 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.

### ðŸ’¬Â  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.

Keep learning new methods and keep growing!

## Related Articles

<< Go Back to Line Break in Excel | Text Formatting | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

1 Comment