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

How to Use Text to Columns Feature with Carriage Return in Excel

While working in Excel, we often need to use the Text to Columns feature of Excel to split data into multiple columns. In the Text to Column option, we need to use a delimiter based on which we will separate the data. Carriage Return is one of the delimiters that are used in Excel. It basically creates a new line. Generally, in other software, we use the ENTER key. But in Excel, we need to use ALT + ENTER to create a new line in a cell. In this article, we will learn 3 easy steps to use the Text to Columns option with carriage return in Excel. So, let’s start the article and explore these steps.


Download Practice Workbook


3 Simple Steps to Use Text to Columns Feature with Carriage Return in Excel

In this section of the article, we will learn 3 simple steps to use Text to Columns option with carriage return in Excel. Let’s say, we have the Personal Details of Employees of a company as our dataset. In the dataset, we have all the details of an employee in a single cell. Our goal is to use the Text to Columns feature with carriage return to split them into separate cells.

excel text to columns carriage return


Step 01: Select Data Type

In the first step, we will choose the data type of our texts. Let’s follow the steps mentioned below to do this.

  • Firstly, select the cells of the Personal Details column and go to the Data tab from Ribbon.
  • After that, click on the Text to Columns option from the Data Tools group.

Select Data Type to use the Text to Columns option with carriage return in Excel

As a result, the Convert Text to Columns Wizard dialogue box will open on your worksheet.

  • Now, select the Delimited option in the Original data type field.
  • Then click on Next.

Read More: [Fixed!] Excel Text to Columns Is Deleting Data


Step 02: Choose the Delimiter

Now, we need to select a delimiter based on which we will split the cell contents into multiple columns.

  • Firstly, check the box of the Other field and click on the blank box beside it.
  • Then, use the keyboard shortcut  CTRL + J.
  • After that, click on Next.

Note: After using the keyboard shortcut, you will not see anything in the blank box beside the Other field. But you can see the Data Preview in the bottom section, and this is exactly what we are after.

Choose the Delimiter to use the Text to Columns option with carriage return in Excel

Read More: How to Convert Text to Columns with Multiple Delimiters in Excel


Step 03: Define the Destination Cell

In this step, we will define the destination cell where we want to display the data. If we don’t select any destination cell, Excel will replace the cell with the original data (cells of the Personal Details column). Let’s use the steps outlined below to select a destination cell.

  • Firstly, click on the marked area of the following picture.

Define the Destination Cell to use the Text to Columns option with carriage return in Excel

  • After that, choose your preferred destination cell. Here, we selected cell C5 as our destination cell.
  • Then, click on the marked portion of the image below.

  • Finally, click on Finish.

Consequently, your data will be split into 4 columns by using the Text to Column option of Excel with carriage return.

Final output of method 1 to use the Text to Columns option with carriage return in Excel


Application of Text to Columns Feature Using Word Delimiter in Excel

In this section of the article, we will discuss the procedure to use a word as delimiter in the Text to Columns feature of Excel. Generally, we use space or a symbol that is common in all the cells to separate the data into different columns. Here, we will replace a common text with a symbol and use that as a delimiter. For instance, we have the Name and Account Number of some employees of a company in a single cell. Our goal is to separate Name and Account Number using a word as a delimiter.

Application of Text to Columns Feature Using Word Delimiter in Excel

Now, let’s follow the steps mentioned below.

Steps:

  • Firstly, use the keyboard shortcut CTRL + H to open the Find and Replace dialogue box on your worksheet.

  • Following that, type in “Acc:” in the Find what field as it is the common text in all cells.
  • Then, type “,” in the Replace with field.
  • Now, click on Replace All.

Using Find and Replace to apply Text to Columns Feature Using Word Delimiter in Excel

  • After that, click on OK as marked in the following image.

  • Next, click on Close in the Find and Replace dialogue box.

As a result, you will have the following output in the Personal Details column.

  • Subsequently, check the box before Comma in the Delimiters section.
  • Following that, click Next.

Using comma as delimiter to apply Text to Columns Feature Using Word Delimiter in Excel

  • Afterward, click on the marked area in the image below to select the destination cell.

  • Then, choose the destination cell. Here, we selected cell C5 as our destination cell.
  • Next, click on the marked area of the following picture.

Selecting destination cell to apply Text to Columns Feature Using Word Delimiter in Excel

  • Finally, click on Finish.

Consequently, you will have the following final outputs as demonstrated in the following picture.

Final output of method 2 to apply Text to Columns Feature Using Word Delimiter in Excel

Read More: How to Use Line Break as Delimiter in Excel Text to Columns


How to Remove Carriage Return with Text to Columns in Excel

In Excel, we often encounter such datasets where all the data are cramped up using a carriage return, as demonstrated below. Although using a carriage return makes a dataset look clean, it is not suitable for doing any analysis. For this reason, we need to remove carriage return with Text to Columns in Excel. We are going to use the VBA Macro option of Excel to do this.

Removing Carriage Return with Text to Columns in Excel

Consequently, you will have the following output in your worksheet, as demonstrated in the image below.


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

Practice section to use the Text to Columns option with carriage return in Excel


Conclusion

So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to use text to column option with carriage return in Excel. If you have any questions, suggestions, or feedback related to this article you can comment below. You can also have a look at our other useful articles on Excel functions & formulas on our website ExcelDemy.


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo