Separate Text In Excel

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Nannila Jai Ratna

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Is 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.

Separate Text In Excel Example

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.

Separate Text In Excel Example - Step 4

Likewise, we can use separate text in excel method.

Table of contents
  • Separate text in Excel is a function used for separating text in worksheet.
  • Excel separates text with appearance of tab, semicolon, comma, space and other symbols.
  • There are two methods to separate Texts in Excel such as Text to Columns in Excel and formulas in Excel.
  • The Delimited and fixed with options are splits text in Excel.
  • Excel Formulas such as Len in excel is used to calculate the length of the string and separate the values based on the position.

How To Separate Text In Excel?

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.

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.

Below are the steps of separating text in excel -

  1. First, we must select the column containing the data, A column.


    Using Delimiter Method Step 1

  2. Under the "Data" tab in the "Data Tools" section, click on "Text to Columns."


    Using Delimiter Method Step 2

  3. A dialog box appears for text to columns wizard.


    Using Delimiter Method Step 3

  4. As we will use the delimiter method first, select the "Delimited" option and click on "Next." Another dialog box appears.


    Using Delimiter Method Step 5

  5. Our texts are separated in Excel by spaces for the current data, so select the "Space" option as a "delimiter" ( By default, Excel selects a tab as a delimiter). Then, finally, click on "Next."


    Using Delimiter Method Step 5

  6. Our "Data preview" shows that our texts are separated with first and last names. Click on "Finish" to see the result.


    Using Delimiter Method Step 6

  7. We have successfully separated our Excel text by using the text-to-column delimiter method.


    Using Delimiter Method Step 7

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.

Using Fixed Width Method Step 1

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.

Using Fixed Width Method Step 2

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.

Using Fixed Width Method step 3

A dialog box appears for converting text to columns wizard.

Using Fixed Width Method step 4

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

Using Fixed Width Method step 5

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

Using Fixed Width Method step 6

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

Using Fixed Width Method step 7

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

Using Fixed Width Method step 8

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

Using Fixed Width Method step 9

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

Using Fixed Width Method step 10

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

Using Fixed Width Method step 11

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:

Separate Text using formula step 1

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.

Separate Text using formula step 2

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.

Separate Text using formula step 4

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,

Separate Text using formula step 5

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

Separate Text using formula step 6

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

Separate Text using formula step 7

Important Things To Note

  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.

Frequently Asked Questions (FAQs)

1. What is separate text in Excel?

Separate Text in Excel is a method that helps users split text in Excel worksheet. There are 2 methods – Using Text to columns method and by using Excel formulas.

2. What is the use of separate text in Excel?

The texts in Excel can appear in different ways. For example, maybe there is a text joined by some symbol, or it is like a first name and the last name.

At some point in our life, we will want those texts separated. The problem statement is we will come towards some data where we need to separate those texts from one another. But how do we separate those texts from one another?

There is one very basic way to do it: copy the desired text in each selected individual cell. Else, we can use some pre-given Excel tools to perform.

3. Explain how to use separate text in excel with an example.

Consider the below table showing movie characters’ names. Now, let us learn how to separate the last names in column B.

Separate Text In Excel - FAQ 3

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 text is separated into column B.

FAQ 3 - Step 4

Likewise, we can use separate text in excel method.