Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
Email Address * First Name *
By entering your email address you agree to receive emails from Excel Off The Grid. We'll respect your privacy and you can unsubscribe at any time.

How to split cells in Excel

Split cells - featured image

There are many circumstances where we receive information with multiple data points inside a single cell.  This often occurs when the data’s original intention is slightly different from how we intend to use it.  In these circumstances, we often need to split the cell into its constituent parts.  This post will look at solving this problem and learn how to split cells in Excel.

The scenario we are trying to solve is splitting an individual’s full name into the first and last name components.

Start and finish splitting the data

If we have ten cells in our data set, it’s not a problem; we can do that manually.  But if we have hundreds or thousands of cells, we need to find another way.  Thankfully we can turn to a variety of Excel features.

Download the example file

I recommend you download the example file for this post.  Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download Icon
Download the file: 0051 Split cells in Excel.xlsx

Watch the video


Watch the video on YouTube.

Example data

In all our solutions, we will be using the following data set.

Example Data - Split cells

In this post, we are covering 4 possible options.  Depending on your data, one method may lead to better results than others.  The lower the level of data consistency, the harder the process becomes.

Split cells using Text to Column

Text to columns is a feature that hides in plain sight.  It’s on the ribbon as an icon, but unless you know it’s there and what it does, you may not have used it before.

The steps to split a cell with Text to Columns are:

  1. Select the cells containing the text to be split
  2. From the ribbon, click Data > Text to Columns
    Ribbon Text to Columns
  3. The Convert Text to Columns Wizard dialog box will open
  4. Select the Delimited option.  This allows us to split the text at each occurrence of specific characters.  In our case, the space character is our delimiter.  Click Next.
    Convert Text to Columns - Step 1
  5. In the Delimiters group, select the relevant option for your data.  I have selected Space for this scenario.  Click Next.
    Convert Text to Columns - Step 2 - Space Character
  6. In Step 3, we specify the data format for the result.  As we are using text, the General format is a reasonable option.  Select a Destination cell where to output the result. I have selected Cell B2. Click Finish.
    General Format - Finish

That’s it.  Just a few clicks and we’re done 🙂

Our range should now be perfectly split into multiple cells.

Final data after splitting the cell

Notes about Text to Columns

Text to columns is not a dynamic feature; if the source data changes, we need to re-run the process.

Text to columns works best on data that is in a consistent format.  For example, if some data points had 1 space, while others had 2 spaces, it may not provide the desired results.

Split cells using Flash Fill

Flash Fill is a fantastic feature that was introduced in Excel 2013.  You may have already used Flash Fill without being aware of it.

Flash Fill can run in two ways:

  • In the background, Excel tries to find patterns in the data and automatically makes suggestions.
  • Triggered by the user at specific times

Background execution

For background execution:

  1. With the source data on the left, type the text element we want to extract in the first row.
    Flash Fill enter first cell
    Nothing will happen yet.
  2. Start typing the element of the text we want to extract from the second cell.  Now Flash Fill jumps into action and makes recommendations:
    Flash Fill triggered automatically
  3. Press the Tab or Enter key to accept the recommended fill.

This gives us the first name.

Flash Fill - First Name only

Manual execution

Now let’s manually trigger Flash Fill to get the last name.

  1. With the source data on the left, type the text element to be extracted into any single row.
    Flash Fill - Manually with any value
  2. Select all the cells in the range to be filled with values (cells C2 to C11 in our example), click Home > Fill (dropdown) > Flash Fill.
    Home - Fill - Flash Fill

And we’re done. Both first and last names have been separated into columns.  I’ve shown the background and manual methods above; you can use either.

Flash Fill - Complete

Notes about Flash Fill

While Flash Fill is very good at identifying patterns, it will not always return the values you are expecting in more complex scenarios.  There needs to be some logical pattern for Flash Fill to find.

Flash Fill returns static values.  If the source data changes, we need to re-run the process.

Flash Fill is also available in Data > Flash Fill
Data - Flash Fill

If Flash Fill is unable to understand the pattern, it will return an error message.
Flash Fill Error

Formula Magic with Dynamic Arrays

Of all the features available in Excel dynamic arrays provide the most power for the smallest time investment.  Yet most Excel users do not even know what they are.

Have you ever faced these spreadsheet scenarios?

  • How can I use VLOOKUP to return all the matching items, not just the first?
  • How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
  • How can I quickly create unique lists of items to use with my SUMIFS calculation?
  • How can I stop copying down formulas every time my source data changes.
  • How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.

Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂

Split cells using Power Query

Another option for splitting cells in Excel is to use Power Query.  This has been natively available in Windows since Excel 2016.  Prior to that, there was an add-in for Excel 2010 and 2013.

First, let’s load our data into the Power Query editor.

  1. Select any cell in the data set, then click Data > From Table/Range from the ribbon.Data - From Table Range
  2. If the data is not in an Excel table already, the Create Table dialog box opens.   Ensure the data range is selected, and my table has headers option is checked.  Then click OK.
    Create Table - Then Click OK
  3. The Power Query editor will open with the selected data displayed.
  4. From the Power Query ribbon select Home > Split Column (drop-down) > By Delimiter.
    Home - Split Column by Delimiter
  5. Depending on the complexity of your data, there are lots of options to split by different positions.  For our scenario, the Space character and each occurrence of the delimiter are good options.  Click OK.
    Split Column be Delimiter
  6. We now have the name split into two different columns.
    Split data in Power Query
  7. Double click the header and change the headings to First and Last respectively.
  8. Now we need to get the data back into Excel.  Click Home > Close & Load (drop-down) > Close & Load To…
    Close and load to Power Query
  9. From the Import Data dialog box, select Table and choose where to load the table to. In the example, I have selected an existing sheet at cell C1.  Click OK.
    Import Data dialog box

The transformed data will now be loaded back into the worksheet.

Text split and reloaded from Power Query

Notes about Power Query

If our source data changes or more data is added to the table, we can simply click Data > Refresh All, to refresh the output.

This method will change the source data into an Excel table even if you don’t want it to.

If splitting by delimiter is not powerful enough, Power Query can transform more complex strings using the Column from Examples feature.

Power Query provides a lot of advanced tools to handle more complex data manipulation.  If you’ve not used Power Query much, then check out my Introduction to Power Query series.

Split cells using Excel formulas

Our final option is to use standard Excel formulas.  These give us the ability to split text using any rules we can program into our formulas.  While formulas are very powerful, they also require our skill rather than using a point-and-click interface.

Useful functions for splitting cells

The most useful functions for splitting cells are:

LEFT

Returns the specified number of characters from the start of a text string.

Example:

=LEFT("Doris Green", 5)

Result: Doris

The second argument of 5 is telling the function to return the first 5 characters of the text “Doris Green”.

RIGHT

Returns the specified number of characters from the end of a text string.

Example:

=RIGHT("Kathryn West", 4)

Result: West

The second argument of 4 is telling the function to return the last 4 characters of the text “Kathryn West”.

MID

MID returns the characters from the middle of a text string, given a starting position and length.

Example:

=MID("Ann Lewis", 5, 3)

Result: Lew

The second argument represents the nth character to start from, and the last argument is the length of the string.  From the text “Ann Lewis”, the 3 characters starting at position 5 are “Lew”.

LEN

Returns the number of characters in a text string.

Example:

=LEN("Amaliya Dixon")

Result: 13

There are 13 characters in the string (including spaces).

SEARCH

SEARCH returns the number of characters at which a specific character or text string is first found.  SEARCH reads left to right and is not case sensitive.

Example:

=SEARCH(" ","Charles Bell",1)

Result: 8

The space character first appears in the 8th position of the string “Charles Bell”; therefore, the value returned is 8.

The last argument is optional; it provides the position to start searching from.  If excluded, the count will start at the first character.

Note: FIND is the case-sensitive equivalent of the SEARCH function.

SUBSTITUTE

SUBSTITUTE replaces all existing instances of a text string with a new text string.

Example:

=SUBSTITUTE("Juan Dixon","Dix","Wils",1)

Result: Juan Wilson

The text string of Dix has been replaced with Wils.  The last argument determines which instance to replace.  In the formula above, it has replaced the 1st instance only.  If we had excluded this argument, it would have replaced every instance.

Example scenario

Our scenario is reasonably simple, so we can use LEFT, RIGHT, LEN and SEARCH functions.  We can extract the first and last name as follows:

First name:

=LEFT(A2,SEARCH(" ",A2)-1)
  • SEARCH finds the space character’s position, which for the first record in the example data set of “Amaliya Allen” is the 8th position.
  • We do not want the space character included in the string, so we minus 1 from the result to give us a value of 7.
  • LEFT then extracts the first 7 characters of the text string.

Last name:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))
  • LEN finds the length of the text string, which for “Amaliya Allen” is 13.
  • SEARCH finds the position of the space character, which is 8.
  • 13 minus 8 provides us with the number of characters after the first space.
  • RIGHT is then used to extract the characters after the space.

With these two formulas, it gets us back to the same result as we’ve had before:

Using Formulas to split text in a cell

Notes about using formulas

Formulas are the only option in this post that are fully dynamic.  If the original data changes, the formula result updates automatically too.

Depending on the complexity of the text string to be split, these formulas can become quite complex.

Conclusion

With these 4 techniques, you can split cells in Excel.  Each option has added more and more power to deal with complexity.  Which option you choose comes down to your specific scenario.  Familiarize yourself with all the options, and then you’ll be able to make the best decisions.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

2 thoughts on “How to split cells in Excel

  1. David N says:

    Rather than the math game of LEN minus SEARCH, my favorite way to get the right side of a split is using REPLACE. That way it looks and feels very similar to the use of LEFT to get the first part.
    =LEFT(A2,SEARCH(” “,A2)-1)
    =REPLACE(A2,1,SEARCH(” “,A2),””)

    • Excel Off The Grid says:

      Hi David – That’s clever, I like it.

      Rather than extracting everything from the right, you’re removing everything from the left. 🙂

Leave a Reply

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