How to Replace Carriage Return with Comma in Excel (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


How to Replace Carriage Return with Comma in Excel: 3 Ways

First, get introduced to the dataset we’ll use to explore the methods. It contains three cloth’s names, sizes, and colors.

Replace Carriage Return in Excel with Comma


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.

Using SUBSTITUTE Function to Replace Carriage Return with Comma

  • Next, use the Fill handle tool to copy the formula for the other cells.

Using SUBSTITUTE Function to Replace Carriage Return with Comma

Now take a look, all the carriage returns are replaced and returned in a line.

Read More: How to Insert Carriage Return in Excel Cell 


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.

Applying Find and Replace Tool to Replace Carriage Return with Comma

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

Applying Find and Replace Tool to Replace Carriage Return with Comma

Soon after, a notification box will show you that it replaced how many carriage returns.

Read More: Remove Carriage Return in Excel with Text to Columns


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.

Embed Excel VBA to Replace Carriage Return with Comma

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

Embed Excel VBA to Replace Carriage Return with Comma

  • Select the data range.
  • Click on the Macros command from the Developer ribbon.

Embed Excel VBA to Replace Carriage Return with Comma

  • 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:

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.

Embed Excel VBA Code to Replace Carriage Return with Comma

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.

Embed Excel VBA to Replace Carriage Return with Comma

  • 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 Find Carriage Return in Excel


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo