Javatpoint Logo
Javatpoint Logo

How to Separate text in Excel

Separate text is one of the basic functions in Excel. It helps you to improve the documents for better filtering, sorting, as well as analysing.

There are the following methods to Separate text in Excel -

Method 1: Separate text in Excel using Text to Columns

Text to Columns feature is the most commonly used method for separating text. This method divides a single cell into two or more cells in Microsoft Excel 2010, 2013, 2016, 2019, and earlier. In this method, a specified delimiter such as a comma, semicolon, space, tab, or other is used to separate text.

For example, you can separate the First Name and Last Name from a single column (Full Name) into two separate columns.

Follow the below-given step by step procedure to separate text in Excel -

Step 1: Select or highlight the cells or columns that contain the text you want to separate.

How to Separate text in Excel

Step 2: Go to the Data tab on the Ribbon. Click on the Text to Columns tab in the Data Tools section.

How to Separate text in Excel

Step 3: A Convert Text to Columns Wizard will appear on the screen with selected Delimited - Characters such as commas or tabs separated each field radio button. Click on the Next button at the bottom of Wizard.

How to Separate text in Excel

Step 4: Select your preferred Delimiters according to your data under the Delimiters section. See a preview of your data in the Data preview section. Click on the Next button at the bottom of Convert Text to Columns Wizard.

Note: In our case, we use Space Delimiters for our data.

How to Separate text in Excel

Step 5: Now, In the next Wizard following options will appear -

General - It is used to convert numeric values to numbers, data values to dates, and keep remaining as text.

Text - It is used to convert all values to text format.

Date - It is used to convert all values to date formats like MDY, DMY, YMD, DYM, MYD, and YDM.

Select the General radio button, enter the data destination in the Destination section, and click on the Finish button.

How to Separate text in Excel

The screenshot below shows that Text (Full Name) is separated into two columns (First Name and Last Name).

How to Separate text in Excel

Method 2: Separate text into columns using functions

Microsoft Excel allows us to use a combination of the LEFT, RIGHT, LEN, SEARCH, as well as FIND functions to separate text.

Separate First Name and Last Name

1. We use the below formula to separate the First Name from the Full Name -

=LEFT(A4, SEARCH(" ",A4,1))

The LEFT function is used to separate the First Name.

A4 is the cell containing the Full Name.

SEARCH function is used to find the value for num_char.

2. We use the below formula to separate the Last Name from the Full Name -

=RIGHT(A4,LEN(A4)-SEARCH(" ",A4,1))

The RIGHT function is used to separate the Last Name.

A4 is the cell containing the Full Name.

SEARCH and LEN function is used to find the value for num_char.

Steps to Separate First name and Last into columns

Step 1: Open a new or an existing Microsoft Excel document.

Step 2: Type or select the text that you want to be separate.

Step 3: Place the cursor on the cell where you want to display the First Name. Type formula =LEFT(A4, SEARCH(" ",A4,1)) in the selected cell.

How to Separate text in Excel

Note: In our case, we use the cell B4 to display the First Name.

Step 4: Press Enter key to separate the First Name from the Full Name.

How to Separate text in Excel

Step 5: Place the cursor on the cell where you want to display the Last Name. Type formula =RIGHT(A4,LEN(A4)-SEARCH(" ",A4,1)) in the selected cell.

How to Separate text in Excel

Step 6: Press the Enter Key from the keyboard to display the Last Name.

The below-given screenshot shows that First Name and Last Name separate from the Full Name.

How to Separate text in Excel

Steps to Separate First Name, Mid Name, and Last Name in separate columns

Microsoft Excel allows us to separate the First Name, Mid Name, and Last Name in separate columns -

1. We use the below formula to separate the First Name from the Full Name -

=LEFT(A5, SEARCH(" ",A5,1))

LEFT function is used to separate the First Name from the Full Name.

A5 is the cell containing the Full Name.

SEARCH function is used to find value for num_chars.

We use the below formula to separate Middle Name from the Full Name -

=MID(A5,SEARCH(" ",A5,1)+1,SEARCH(" ",A5,SEARCH(" ",A5,1)+1)-SEARCH(" ",A5,1))

The MID function is used to separate Midden Name from the Full Name.

A5 is the cell containing the Full Name.

We use the below formula to separate the Last Name from the Full Name -

=RIGHT(A5,LEN(A5)-SEARCH(" ",A5,SEARCH(" ",A5,1)+1))

The Right function is used to separate the Last Name from the Full Name.

SEARCH and LEN function is used to find value for num_chars.

Steps to separate First Name, Mid Name, as well as LAST Name in different columns

Follow the below given steps to separate First Name, Mid Name, as well as LAST Name from the text -

Step 1: Open a New or an existing Microsoft Excel document.

Step 2: Place the cursor on the cell where you want to see the First Name and type formula =LEFT(A5, SEARCH(" ",A5,1)) in the selected cell.

How to Separate text in Excel

Step 3: Press the Enter key from the keyboard. You can see that First Name will appear in the selected cell.

How to Separate text in Excel

Step 4: Place cursor on the cell where you want to display middle name. Type formula =MID(A5,SEARCH(" ",A5,1)+1,SEARCH(" ",A5,SEARCH(" ",A5,1)+1)-SEARCH(" ",A5,1)) in the selected cell.

How to Separate text in Excel

Step 5: Press the Enter key to display middle name in the selected cell.

How to Separate text in Excel

Step 6: Place cursor on the cell where you want to be display the Last Name. Type formula =RIGHT(A5,LEN(A5)-SEARCH(" ",A5,SEARCH(" ",A5,1)+1)) in your desired cell.

How to Separate text in Excel

Step 7: Press the Enter key from the keyboard.

Now, you can see that First Name, Middle Name, as well as Last Name separate from the given text.

How to Separate text in Excel

Method 3: Separate text in a Specified Position

In Microsoft Excel, you can also separate text in a specified position using the Left, Mid, and Right functions -

Left function - In Excel, Left function is used to separate a speified number of characters from the beginning (Left).

Mid function - In Excel, Mid function is used to separate a specified number of characters from the mid (middle).

Right function - In Excel, Right function is used to separate a specified number of characters from the end (Right).

Steps to separate text from the specified location

There are the following steps to separate text from the specified location -

Step 1: Open a new Microsoft Excel document.

Step 2: Type the text that you want to separate.

Step 3: Place the cursor on the cell where you want to see the separated text.

  1. Type formula =LEFT("javaTpoint", 4) to separate text from the beginning. (See Cell B1 in the screenshot).
  2. Type formula =MID("javaTpoint", 5, 1) to separate text from the middle. (See the cell B2 in the screenshot).
  3. Type formula =RIGHT("javaTpoint", 5) to separate text from the end (See the cell B3 in the screenshot)
How to Separate text in Excel

Method 4: Separate Text From Numbers

Microsoft allows you to separate the text containing numbers into two different columns.

Steps to Separate Text from Numbers

Steps to Separate Text from Numbers is as follows -

Step 1: Open a Microsoft Excel document that contains text you want to separate.

Step 2: Select or highlight the columns that contain text with numbers.

How to Separate text in Excel

Step 3: Go to the Data tab on the Ribbon and click on the Text to Columns option in the Data Tools section.

How to Separate text in Excel

Step 4: A Convert Text to Columns Wizard will appear on the screen with the selected Delimited radio button. Click on the Fixed width radio button and click on the Next button at the bottom of the screen.

How to Separate text in Excel

Step 5: Adjust the width using vertical divider lines in the Data Preview section and click on the Next button as shown in the given screenshot.

How to Separate text in Excel

Step 6: In the Next Wizard, click on the Advanced button.

How to Separate text in Excel

Step 7: An Advanced Text Import Settings dialog box will appear in which select the decimal separator and the thousand separator. Click the OK button.

How to Separate text in Excel

Step 8: Type or browse your desired destination in the Destination text box and click on the Finish button.

How to Separate text in Excel

The below-given screenshot shows that numbers are separated from the text.

How to Separate text in Excel




Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA