Separate Text In Excel

Separate text in excel is a method used to separate text to other cells. Excel separates text with the presence of tab, semicolon, comma, space and other symbols.

For example, consider the below table showing names of people. Now, let us learn how to separate the text to separate the second names of people in column B.

The steps are:

Step 1: First, select the cell or range to which we want to separate text. So, in this example, let us select cell range A2:A4.

Step 2: Next, click on Data → Text to columns. The Convert Text to Columns wizard - Step 1 of 3 window appears.

Step 3: Then, click on Delimiter and Next > options.

Similarly, click on Space in the following window, Convert Text to Columns wizard - Step 2 of 3 and then select, Next >>.

Step 4: Finally, check the preview and click on Finish in the Convert Text to Columns wizard - Step 3 of 3 window.

We can see the separated text in column B.

Likewise, we can use separate text in excel method.

There are two methods to separate Texts in Excel:

1. Using “Text to Columns in Excel“: It further has its own two bifurcations:
   1. Delimited: This feature splits the text, which is joined by characters, commas, tabs, spaces, semicolons, or any other character such as a hyphen (-).
   2. Fixed Width: This feature splits the text, which is joined with spaces with a certain width.
2. Using Excel Formulas: We can use formulas like the Len in excel to calculate the length of the string and separate the values by knowing the position of the characters.

Let us learn these methods using examples.

Example #1 - Delimiter Method To Separate Text

First, where do we find this feature text to columns in Excel? It is under the “Data” tab in the “Data Tools” section.

Consider the following data,

We want to separate the first and last names. Therefore, the last names’ content is in the B column.

Example #2 - Using The Fixed Width Method To Separate Text

We already know where the option of text to the column is in Excel. It is in the “Data” tab under the “Data Tools” section.

Now we consider the following data,

There is a survey company that is surveying for the feedback of a restaurant. The users give their feedback as “Good” or “Bad.” But every response is saved by a timestamp, which means with every response, time is recorded, specific data and time in hours and minutes.

We need to separate the date from the time in the data. Below is the data.

Step #1 - We need to separate the contents of column A, but there is data in column B, so we need to insert another column between columns A and B. Select column B and press the “CTRL + +” keys for that.

It adds another column between the columns and shifts the data from previous column B to column C.

Step #2 - Now, select the data in column A. Click on “Text to Columns” under the “Data” tab in the “Data Tools” section.

A dialog box appears for converting text to columns wizard.

Step #3 - This time, we use a fixed width method and click on “Next.” Another “Convert Text to Column Wizard” dialog box appears.

Step #4 - In the “Data preview,” we can see the data is separated into three parts: date, time, and the meridian, AM, and PM.

We need only date and time, so hover the mouse around the second line and double click on it, and it disappears.

Step #5 - Now, click “Next.” Another “Convert Text to Columns Wizard” dialog box appears. Then, you need to click on the “Finish” button.

Now, the data is separated with date in one column and time in another.

Step #6 - The data formatting in column B is incorrect, so we must rectify it. Select the contents on column B, and in the “Home” tab under the “Number” section, the default selection is “Custom.” We need to change it to “Time.”

Step #7 - We have successfully separated our Excel data using the “Text to Columns Fixed Width” method.

Example #3 - Using Formulas For Separating Text

We can also use Excel formulas to separate the text and numbers, which are joined together. Then, we can remove that data by using “Text to Columns.” However, we use formulas for complex situations.

Let us consider the following Data:

We have products in column A with their respective product codes. We need products in one column and product code in another. We want to use formulas to do this.

Step #1 -To this, we must first count the number of digits and separate that number from the character.

Step #2 - We need the “Product Name” in column B  and “Product Code” in column C.

Step #3 - First, we will separate the digits to separate the text in Excel from the data. In C2, write the following Excel formula to separate text.

We use the right to function as we know the numbers are in the right.

Step #4 - Now, in Cell B2, write the following Formula,

Step #5 - Press the “Enter” key. Using the LEN function, we have successfully separated our data from “Product Name” and ” Product Code.”

Step #6 - Drag the formulas to cells B6 and C6, respectively.

1. If we use the “Text to Columns” method, always ensure that we have an additional column so that the data in any column does not get replaced.
2. We must ensure the characters’ positioning if we separate texts in Excel using Excel formulas.