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.
Step 2: Go to the Data tab on the Ribbon. Click on the Text to Columns tab in the Data Tools section.
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.
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.
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.
The screenshot below shows that Text (Full Name) is separated into two columns (First Name and Last Name).
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 –
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 –
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.
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.
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.
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.
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 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 –
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 –
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.
Step 3: Press the Enter key from the keyboard. You can see that First Name will appear in the selected cell.
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.
Step 5: Press the Enter key to display middle name in the selected cell.
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.
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.
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.
- Type formula =LEFT(“tutoraspire”, 4) to separate text from the beginning. (See Cell B1 in the screenshot).
- Type formula =MID(“tutoraspire”, 5, 1) to separate text from the middle. (See the cell B2 in the screenshot).
- Type formula =RIGHT(“tutoraspire”, 5) to separate text from the end (See the cell B3 in the screenshot)
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.
Step 3: Go to the Data tab on the Ribbon and click on the Text to Columns option in the Data Tools section.
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.
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.
Step 6: In the Next Wizard, click on the Advanced button.
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.
Step 8: Type or browse your desired destination in the Destination text box and click on the Finish button.
The below-given screenshot shows that numbers are separated from the text.
