Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Remove Carriage Return in Excel with Text to Columns

Sometimes, our Excel data is extracted or built in an improper format. It occurs very frequently that we get data with multiple lines containing carriage returns. Now, for proper data format and visualization, it is a must to remove carriage return and split the text into multiple columns in Excel. In this article, I will show you () methods to remove carriage return in Excel text to columns.


Download Practice Workbook

You can download our practice workbook from here for free!


What Is Carriage Return?

Carriage return is mainly a special character that is used to create a new line of a text and continue the text from the beginning of the new line. It is also commonly known as cartridge return, return, and CR.

It is generally created by hitting the Enter button. But, in the Excel cell, you need to press Alt + Enter to create a carriage return.

Carriage return gives fresher look to the data, explanation, visualization, etc. You can start a new para with this character very easily.


Steps to Remove Carriage Return with Text to Columns Feature in Excel

Say, we have an information set of 5 people containing their names, professions, and countries. This information is written in one cell with a carriage return. Now, we want to remove carriage return in Excel text to columns. You can follow the simple steps below to achieve this.

Sample Data to Remove Carriage Return in Excel Text to Columns


📌 Step 1: Access the Text to Columns Tool

First, you need to access the Text to Columns tool properly.

  • To do this, at the very beginning, select the information set (B5:B9 here).
  • Subsequently, go to the Data tab >> Data Tools group >> Text to Columns tool.

Access the Text to Columns tool to Remove Carriage Return

Read More: How to Find Carriage Return in Excel (2 Easy Methods)


📌 Step 2: Finish ‘Convert Text to Columns Wizard’ Setup

  • Now, the Convert Text to Columns Wizard – Step 1 of 3 dialogue box will appear.
  • Subsequently, put the radio button on the Delimited option and click on the Next button.

Convert Text to Columns Wizard - Step 1 of 3 Window

  • Afterward, the Convert Text to Columns Wizard – Step 2 of 3 window will appear.
  • Here, check on the Other: option’s checkbox only.
  • Following, press Ctrl + J in the text box beside the Other: option.
  • Last but not the least, click on the Next button.

Convert Text to Columns Wizard - Step 2 of 3 Window to Remove Carriage Return

  • Now, the Convert Text to Columns Wizard – Step 3 of 3 dialogue box will appear.
  • Now, choose the General option from the Column data format group.
  • Following, put the reference as D5 in the Destination: text box.
  • Finally, click on the Finish button.

Convert Text to Columns Wizard - Step 3 of 3 Window

  • As a result, a Microsoft Excel dialogue box will appear warning you about data replacement.
  • Click on the OK button.

Confirm the Removal of Carriage Return in Excel Text to Columns

Thus, you can remove carriage return in Excel text to columns. And, for example, the output should look like this.

Removed Carriage Return in Excel Text to Columns

Read More: How to Insert Carriage Return in Excel Cell (3 Simple Ways)


Remove Carriage Return in Excel with a VBA Code

You can also use VBA code to remove carriage return and split your information into pieces at different cells. Follow the steps below to accomplish this using VBA code.

📌 Steps:

  • First and foremost, go to the Developer tab >> Visual Basic tool.

Access the Visual Basic Tool

  • At this time, the Microsoft Visual Basic for Applications window will appear.
  • Click on Sheet3 from the VBAProject group.

Microsoft Visual Basic for Applications window

  • Subsequently, a code window will appear for this sheet.
  • Afterward, write the code below in the code window.
  • Following, press Ctrl + S to save the code.

Write and Save the Code to Remove Carriage Return in Excel Text to Columns

  • Now, a Microsoft Excel window will appear about the confirmation of saving.
  • Click on the No button.

Confirm the Saving of the Code

  • As a result, the Save As dialogue box will appear.
  • Now, choose the Save as type: option as .xlsm file and click on the Save button.

Save the Excel as .xlsm File

  • Afterward, close the code window.
  • Following, select the cell where you want the split >> go to the Developer tab >> Macros tool.

Access the Macros Tool

  • As a result, the Macros window will appear.
  • Choose the RemoveCarriageReturn macro from the list and click on the Run button.

Run the Code to Remove Carriage Return in Excel Text to Columns

  • At this time, the Remove Carriage Return window will appear.
  • Write B5 in the Range text box.
  • Finally, click on the OK button.

Remove Carriage Return Window

As a result, the B5 cell’s carriage return is removed and the data are split. For an instance, the outcome should look like this now.

Removed Carriage Return in Excel


💬 Things to Remember

  • You can insert a carriage return in a cell by pressing Alt + Enter in the selected cell.
  • It is better to turn on the Wrap Text option from the Excel Home tab for seeing the full data with carriage returns.
  • You can use Alt + 0010 from the numeric keyboard instead of Ctrl + J in the Other: text box.

Conclusion

In a nutshell, in this article, I have shown you step-by-step guidelines to remove carriage return in Excel text to columns. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative.

Moreover, you are very welcome to comment here if you have any further questions or recommendations.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo