Md. Sourov Hossain Mithun
If you have a lot of carriage returns in your dataset and finding ways to replace them all with commas then you have come to the right place. Here, we’ll show 3 quick and effective methods to replace carriage return in Excel with comma.
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
What Is Carriage Return in Excel?
The tool we used to push a cell’s content to a new line within the same cell in Excel is called Carriage return. It is also called line break in Excel.
3 Ways to Replace Carriage Return in Excel with Comma
First, get introduced to the dataset we’ll use to explore the methods. It contains three cloth’s names, sizes, and colors.
1. Using SUBSTITUTE Function to Replace Carriage Return with Comma
In the beginning, we’ll remove the carriage returns with a comma by using the SUBSTITUTE function. It takes only three arguments.
Steps:
- Insert the following formula in Cell C5–
=SUBSTITUTE(B5,CHAR(10),",")- Then hit the ENTER button to get the output.
- Next, use the Fill handle tool to copy the formula for the other cells.
Now take a look, all the carriage returns are replaced and returned in a line.
Read More: How to Replace Text with Carriage Return in Excel (4 Smooth Approaches)
2. Applying Find and Replace Tool to Replace Carriage Return with Comma
If you want to avoid functions then there’s another way. The Find and Replace tool can easily do the task smartly. It’s quite helpful for a large worksheet because it can remove all the carriage returns in a while from your sheet.
Steps:
- Firstly, copy the cells into a new column.
- Later, select the cells and press CTRL + H from your keyboard, and soon the Find and Replace tool will open up.
- Click on the Find what box and press CTRL + J.
- After that, type a comma in the Replace with box.
- Finally, just press Replace All box.
Soon after, a notification box will show you that it replaced how many carriage returns.
Read More: Carriage Return in Excel Formula to Concatenate (6 Examples)
3. Embed Excel VBA to Replace Carriage Return with Comma
Excel VBA can convert carriage return to comma more smartly and quickly. We’ll show here two macros by which you will be able to replace Carriage Return in two ways.
3.1. Select Range Before Running Macro
If you are sure about your data range and want to select first before running macro then follow this way.
Steps:
- Press ALT + F11 to open the VBA window.
- Next, click as follows to insert a module- Insert > Module.
- Later, write the following codes in it-
Sub Replace_Carriage_Return_SelectRangeFirst()
For Each Cell In Selection
Cell.Value = Replace(Cell.Value, Chr(10), ",")
Next
End Sub- Then go back to your sheet.
- Select the data range.
- Click on the Macros command from the Developer ribbon.
- After appearing in the Macro dialog box, select the specified macro name and press Run.
And yes! We are done.
3.2. Select Range After Running Macro
When you are not sure about your data range and want to decide after running the macro then follow this way.
Steps:
- Follow the first two steps from the previous section to insert a new module
- Then type the following codes in it-
Sub Replace_Carriage_Return_SelectRangeLater()
Dim mRange As Range
Dim mWorkRange As Range
On Error Resume Next
xTitleId = "Replace Carriage Return"
Set mWorkRange = Application.Selection
Set mWorkRange = Application.InputBox("Select the Range", xTitleId, mWorkRange.Address, Type:=8)
For Each mRange In mWorkRange
mRange.Value = Replace(mRange.Value, Chr(10), ",")
Next
End Sub- Next, turn back to your worksheet.
Code Breakdown:
- First , I created a Sub procedure- Replace_Carriage_Return_SelectRangeLater().
- Next, declared two variables- mRange As Range and mWorkRange As Range.
- Then Selection will select the data range.
- InputBox will create an input box to set the data range.
- Finally, mRange.Value will replace all the carriage returns with a comma.
- Select the macro name and press Run.
- A dialog box will appear to choose the data range. Choose the range and just press OK.
See, we got the same output.
Read More: How to Insert Carriage Return in Excel Cell (3 Simple Ways)
Conclusion
That’s all for the article. I hope the procedures described above will be good enough to replace carriage return in Excel with a comma. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.
