How to Split Text in Excel: The 3 Proven Ways to Save Time

Data is not always readily formatted for you to work with. For example, when you import data from an external application, the formatting could be completely different to what you require. The first and last names, addresses, telephone numbers, and or email addresses in a customer or employee data could be contained in one column in continuous text rather than in separate columns. You may need to split text in Excel for various reasons. For example, to sort the data by first or last name or by an address. Similarly, you may need the data to create a pivot table and want separate column headings as pivot table fields.

In this post, I will show you how to separate text in Excel using three easy methods which are:

  1. The Text to Columns tool
  2. Excel text formulas
  3. The Flash Fill tool

1. Split Text in Excel using Text to Columns

Text to Columns is a great tool which allows you to split text in Excel into multiple columns very easily. The Text to Columns tool in Excel allows you to use a common delimiter character to separate text in Excel. A delimiter character is normally a comma, space, semi-colon or a tab but can be any character. The delimiter character shows Excel exactly where to split the text.

Let’s now look at a couple of examples of how to use Text to Columns to split text in Excel.

Split Names into First and Last Name

Excel table which shows the first and last names in column A and column B has the first name header and column C has a last name header.
The first and last names are in one text string in column A and needs to be split into first and last names in columns B and C

In this example, there is a list of first and last names contained in one text string in column A. The goal is to split the first and last names and put them in columns B and C.

Step by Step Instructions

Here are the steps to do this.

  1. Select the dataset that you want to split. In this example, I select the range A2:A7.
  1. From the ribbon, click on the Data tab and under the Data Tools group click on the Text to Columns button.
The Excel ribbon which has the Data tab and the Text to Columns button highlighted in a red border to show how to split text in Excel.
The Text to Columns button is located in the Data tab of the ribbon
  1. This opens the Convert Text to Columns Wizard. There are three steps to complete. In the first step, select the Delimited option. (This is the default option). The delimited option allows you to split text based on where there is a comma, space, tab, semicolon, or any other character. Click on Next.
The Convert Text to Columns Wizard step 1 of 3 and the Delimited option is highlighted in a red border. The Next button is also highlighted in a red border.
Choose the Delimited option in the Convert Text to Columns Wizard in the first step
  1. In step two of the wizard, select the Space checkbox as the delimiter under Delimiters. If you think there may be double/triple consecutive spaces between the names, then put a checkmark against Treat consecutive delimiters as one. Click on Next.
The Convert Text to Columns Wizard step 2 of 3 and the Space checkbox is highlighted in a red border. The Next button is also highlighted in a red border.
Choose the space character as the delimiter
  1. In step three, under Column data format, leave the setting as General. You need to enter the destination cell in the Destination field. In my example, I want to enter the names starting from cell B2, so I select this cell. If you don’t enter a destination cell, it will overwrite the existing dataset with the first name. The second name will go in the next column. Finally, click on the Finish button.
The Convert Text to Columns Wizard step 3 of 3 and the General option and the destination cell is highlighted in a red border. The Finish button is also highlighted in a red border.
Select the General option and choose the destination cell of where the split text will be placed
  1. The data in column A is now split with the first names in column B and the last names in column C.
The first and last names have been split in Excel from column A which contains both the first and last names. The first names are in column B and the last names are in column C.
The first and last names have been split in columns B and C

Points to Note

There are a couple of points to note when you use Text to Columns to separate text in Excel.

  1. If the names have initials or middle names, then this technique might not work correctly. It works well when there is just a first and last name.
  1. The results you get from using Text to Columns is static. This means that if there are any changes to the original data, you will have to repeat the whole process again.

Split Email Addresses into Usernames and Domain Names

You can also specify your own delimiter to split text. Let’s look at an example of how to do this.

An Excel table which shows peoples email address. This table will be used to split text in Excel and the users name will be in column B and the domain names in column C.
The data contains email addresses in column A and this needs to be split into usernames and domain names

In this example, there is a list of email addresses and I want to split these into usernames and domain names. I can do this by specifying the @ sign as a delimiter. The names before the @ will become the usernames and the text after it will become the domain names. The steps to achieve this is very similar to the first example where I split the first and last names.

Step by Step Instructions

Here are the step by step instructions on how to do this.

  1. Select the dataset that you want to split. In this example, I select the range A2:A7.
  1. From the ribbon, click on the Data tab and under the Data Tools group click on the Text to Columns button.
The Data tab and the Text to Columns button is highlighted in a red border in the Excel ribbon.
Click the Text to Columns button in the Excel ribbon which is located in the Data tab under the Data Tools button
  1. The Convert Text to Columns Wizard will open. In the first step of the wizard, select the Delimited option. Click on Next.
The Convert Text to Columns Wizard step 1 of 3 and the Delimited option is highlighted in a red border. The Next button is also highlighted in a red border.
Select Delimited in the Convert Text to Columns Wizard
  1. In the second step, select Other under Delimiters and then enter the @ sign in the field next to it. Deselect all other delimiter options. Click on Next.
The Convert Text to Columns Wizard step 2 of 3 and the Other option is highlighted in a red border. The field next to Other contains a @.
Select Other as the delimiter and then enter the @ sign
  1. In the final step, keep the setting as General under Column data format. Enter the destination cell in the Destination field. In my example, I want to enter the usernames in cell B2, so I select this cell. Finally, click on the Finish button.
The Convert Text to Columns Wizard step 3 of 3 and the General option and the destination cell is highlighted in a red border. The Finish button is also highlighted in a red border.
In the final step, select General and then enter the cell reference of where the split text should go
  1. The usernames are now in column B and domain names in column C.
An Excel table table where text has been split in Excel. Email addresses are in column A, names are in column B and the domain names are in column C.
The email addresses have now been split and the usernames are in column B and the domain names in column C

2. Split Text in Excel using Excel Formulas

Another way you can separate text in Excel is by using the LEFT, FIND, LEN and RIGHT functions. These functions are called text functions because they manipulate text in a cell. Formulas which contain text functions have one big advantage over the the Text to Columns tool. As mentioned earlier, Text to Columns produces static results which means that when the original source is changed, the results stay the same. The advantage of using formulas over Text to Columns is that it gives dynamic results. This means that when the source data changes, so do the results of the formula.

Before I show you how to use these functions to separate text, it is important to understand what each of these text functions does. The table below explains what the goal is for each function, their syntax and what their arguments mean.

FUNCTIONAIMSYNTAXARGUMENTS
LEFTTo extract characters from the left side of a text string=LEFT(text, [num_chars])text – The text you want to use to extract the characters from the left side
num_chars – This is optional. The number of characters you want to extract from the left side
FINDTo find the location of a character in a text string=FIND(find_text, within_text, [start_num])find_text – The character you want to find in the text string
within_text – The text string to search in
start_num – This is optional. The starting position in the text string to search
LENTo get the length of a text string=LEN(text)text – The text to use to calculate its length
RIGHTTo extract characters from the right side of a text string=RIGHT(text, [num_chars])text – The text you want to use to extract the characters from the right side
num_chars – This is optional. The number of characters you want to extract from the right side
Table which shows the aim of each text function, their syntax and what their arguments mean

Split the First and Last Names using Text Functions

Excel table which contains first and last names in column A. Column B has a first name header and column C has a last name header.
The first and last names are in column A in one text string and need to be split in columns B and C using Excel formulas

I will use the same data as the one I used earlier to split the first and last names using the Text to Columns tool.

Extracting the First Name

The LEFT and FIND functions are used to separate text in Excel by extracting the first names in column A and entering it in column B.
The LEFT and FIND functions are used to create a formula to extract the first name

In cell B2 I created a formula which extracts the first name. The formula is:

=LEFT(A2,FIND(” “,A2)-1)

The FIND function finds the first space character between the first and last name in cell A2. The first space is located in position 6 after “Jason”. The LEFT function then extracts all the characters before the space character. The -1, at the end of the formula, removes the space character located in position 6. Finally, I copied the formula down to cell B7.

Extracting the Last Name

The RIGHT, LEN and FIND functions are used to split text in Excel by extracting the last names in column A and entering it in column C.
The RIGHT, LEN and FIND functions are used to extract the last names

In cell C2 I created a formula to extract the last name which is:

=RIGHT(A2,LEN(A2)-FIND(” “,A2))

The FIND function is used to get the position of the space character after the first name “Jason”. In this example, it is in position 6. It then subtracts this number from the length of the text given by the LEN function. This returns the number of characters in the last name. The RIGHT function is then able to extract the last name. Finally, I copied the formula down to cell C7.

3. How to Split Text in Excel using Flash Fill

Flash Fill is a data tool that will guess the pattern of what you are typing in a few rows and then fills the rest of the data for you. It was introduced in Excel 2013. Flash Fill will save you lots of time in data entry. Flash Fill works automatically after entering a few examples, if enabled in Excel options. It will show a preview in light grey and then you can accept the results by pressing Enter on your keyboard.

Where is Flash Fill Located in Excel?

The Data tab and the Flash Fill button is highlighted in a red border in the Excel ribbon.
Flash Fill is located in the Data tab under the Data Tools group

The Flash Fill button is in the Data tab of the Excel ribbon, under the Data Tools group.

The Home tab and the Fill button is highlighted in a red border in the Excel ribbon.
The Flash Fill button can also be found in the Home tab of the ribbon

It can also be found in the Home tab under the Editing group of the Excel ribbon. Click on the Fill button and a shortcut menu will appear. From the menu click on Flash Fill.

There are three ways you can use Flash Fill to separate text in Excel. These are by:

  1. Entering the text manually until Excel recognises a pattern
  2. Using a keyboard shortcut
  3. Using the mouse
Excel table which contains first and last names in column A. Column B has a first name header and column C has a last name header.

For each method, I will use the same dataset as before and extract the first and last names from column A and put them in columns B and C.

Using Flash Fill to Separate Text in Excel by Entering Text Manually

The Flash Fill tool is used to split text in Excel by entering the first names only in cells B2 and B3. Excel recognises a pattern and the remaining cells in column B shows a preview in light grey.
After entering the first couple of names, Flash Fill will recognise a pattern

To extract the first names, I entered the first two names in cell B2 and B3. Flash Fill automatically recognises the pattern and previews the next entries in light grey.

The Flash Fill tool has filled in the first names in column B.
If you are happy with the preview, press the Enter button

If you are happy with the preview, then just press Enter on your keyboard and the remaining rows will be filled.

The Flash Fill tool is used to separate text in Excel by entering the last names only in cells C2 and C3. Excel recognises a pattern and the remaining cells in column C shows a preview in light grey.
To extract the last name, start entering the last names and Flash Fill will recognise a pattern

Similarly, to extract the last names, follow the same process as when you extract the first name. In this example, I entered the last names in cells C2 and C3 and then Flash Fill recognises the pattern. If you are happy with the preview Excel shows, then just press the Enter button on your keyboard.

One thing to note is that Flash Fill cannot fill upwards. If you start entering data in the middle of the column, then Flash Fill will fill downwards and not up. To overcome this, you can click the Flash Fill button, either from the Data tab or the Home tab in the ribbon, as mentioned earlier. By doing this, the whole column will be filled.

Using the Flash Fill Keyboard Shortcut to Separate Text in Excel

There is also a keyboard shortcut for Flash Fill to split text. The steps to use the keyboard shortcut are as follows.

  1. Type the example data.
  1. Select all the cells in the column you want to fill, including the example data.
  1. Press Ctrl + E on your keyboard.

Using the Mouse to Split Text

The third method to use Flash Fill to split text in Excel is by using the mouse. To do this, follow these steps.

  1. Type the example data. In this example, I typed the name “Jason” in cell B2.
An Excel table which contains the first and last names in column A. The first name is entered in cell B2.
Type the first entry in the cell
  1. Right-click the fill handle which is located on the bottom right of the active cell and drag the mouse down to the bottom row.
  1. Release the right-click and then select Flash Fill from the menu.
The Flash Fill option from the shortcut menu is highlighted in a red border.
Click on Flash Fill from the menu

4. Flash Fill has filled all the the first names in column B. You can then follow the same steps to split the last name.

Flash Fill has filled column B with the first name. The first and last names are in column A.
Flash Fill has filled all the first names

So there you have it. This is a very comprehensive post which shows you three great ways on how to split text in Excel. I hope you enjoyed this post and found it helpful. If you did, then please enter your comments and feedback in the comments section below. I would love to hear from you.

Do you Want to Learn more Excel Tips, Hints and Tricks?

Splitting text in Excel using the Text to Columns tool, text formulas and Flash Fill is just one great way to save time and effort in Excel. If you want to learn more Excel tips and tricks then you must get Excel Bible for Beginners: Excel for Dummies Guide to the Best Excel Tools, Tips and Shortcuts you Must Know.

Excel Bible for Beginners: Excel for Dummies Guide to the Best Excel Tools, Tips and Shortcuts you Must Know by Harjit Suman
Excel Bible for Beginners Book by Harjit Suman

This Excel book will help you work smarter, save time, and become more productive by learning all the best hints, tips and tricks Excel has to offer. This book will show you how to:

  • Hide specific text in a worksheet
  • Quickly insert multiple rows using shortcut keys
  • Shift between lots of open Excel windows
  • Repeat your last actions using just one keystroke
  • Get quick access to your favourite command buttons
  • Use the Camera tool
  • Quickly remove duplicate entries using the Advanced Filter tool
  • Format dates from US to UK format and vice versa
  • Make Excel speak back at you
  • Automatically populate data
  • Change data from column format to row format and vice versa
  • Make your worksheets very hidden
  • Analyse large datasets using Pivot Tables
  • Create two-way lookups
  • Access hidden features that are not available in the ribbon
  • Use some Excel formulas and functions to manipulate data quickly
  • And much more!

Here are just some reviews from Amazon for this book.

5 star Amazon review for Excel Bible for Beginners: Excel for Dummies Guide to the Best Excel Tools, Tips and Shortcuts you Must Know by Harjit Suman.
5 star Amazon review for Excel Bible for Beginners: Excel for Dummies Guide to the Best Excel Tools, Tips and Shortcuts you Must Know by Harjit Suman.
5 star Amazon review for Excel Bible for Beginners: Excel for Dummies Guide to the Best Excel Tools, Tips and Shortcuts you Must Know by Harjit Suman.

So what are you waiting for? To learn more about this book or to purchase it from Amazon, then press either of the button below. If you would like the eBook version, then you can purchase this from my shop.

Spread the love
         
  
        
 
        
 
    

Leave a Comment

Your email address will not be published. Required fields are marked *