How to split cells in Excel: Text to Columns, Flash Fill and formulas

How do you split a cell in Excel? By using the Text to Columns feature, Flash Fill, formulas or Split Text tool. This tutorial outlines all the options to help you choose the technique best suited for your particular task.

Generally, you may need to divide cells in Excel in two cases. Most often, when you import data from some external source where all information is in one column while you want it in separate columns. Or, you may want to separate cells in an existing table for better filtering, sorting or a detailed analysis.

How to split cells in Excel using Text to Columns

The Text to Columns feature comes in really handy when you need to split cell contents into two or more cells. It allows separating text strings by a certain delimiter such as comma, semicolon or space as well as splitting strings of a fixed length. Let's see how each scenario works.

How to separate cells in Excel by delimiter

Suppose, you have a list of participants where a participant name, country and expected arrival date are all in the same column: Data in one cell to be split into several cells

What we want is to separate data in one cell into several cells such as First Name, Last Name, Country, Arrival Date and Status. To have it done, perform the following steps:

  1. If you want to place the results in the middle of your table, start by inserting a new column(s) to avoid overwriting your existing data. In this example, we have inserted 3 new columns like shown in the screenshot below: Insert a new column(s) to avoid overwriting your existing data. If you don't have any data next to the column you want to separate, skip this step.
  2. Select the cells you want to divide, navigate to the Data tab > Data Tools group, and click the Text to Columns button. Click Text to Columns on the Data tab.
  3. In the first step of the Convert Text to Columns wizard, you choose how to split cells - by delimiter or width.In our case, the cell contents are separated with spaces and commas, so we select Delimited, and click Next. Choose how to separate cells - by delimiter or width.
  4. In the next step, you specify the delimiters and, optionally, text qualifier.You can choose one or more predefined delimiters as well as type your own one in the Other box. In this example, we select Space and Comma: Choose one or more predefined delimiters or type your own one.

    Tips:

    • Treat consecutive delimiters as one. Be sure to select this option when your data may contain two or more delimiters in a row, e.g. when there are a few consecutive spaces between words or the data is separate by a comma and a space, like "Smith, John".
    • Specifying the text qualifier. Use this option when some text is enclosed in single or double quotes, and you'd like such portions of text to be inseparable. For example, if you choose a comma (,) as the delimiter and a quotation mark (") as the text qualifier, then any words enclosed in double quotes, e.g. "California, USA", will be put into one cell as California, USA. If you select {none} as the text qualifier, then "California will be distributed into one cell (together with an opening quotation mark) and USA" into another (together with a closing mark).
    • Data preview. Before you click the Next button, it stands to reason to scroll through the Data preview section to make sure Excel has split all cells contents right.
  5. Just two more things are left for you to do - choose the data format and specify where you want to paste the resulting values:
    • Data format. By default, the General format is set for all columns, which works well in most cases. In our example, we need the Data format for the arrival dates. To change the data format for a particular column, click on that column under Data preview to select it, and then choose one of the formats under Column data format (please see the screenshot below).
    • Destination. To tell Excel where you want to output the separated data, click the Collapse Dialog icon Collapse Dialog button next to the Destination box and select the top-leftmost cell of the destination range, or type a cell reference directly in the box. Please be very careful with this option, and make sure there are enough empty columns right to the destination cell. Set the desired format for split cells.

    Notes:

    • If you do not want to import some column that appears in the data preview, select that column and check Do not import column (skip) radio button under Column data format.
    • It is not possible to import the split data to another spreadsheet or workbook. If you attempt to do this, you will get the invalid destination error.
  6. Finally, click the Finish button and you are done! As shown in the below screenshot, Excel has perfectly placed the contents of one cell into several cells: Data in one cell is split into several cells.

How to split text of a fixed width

This section explains how to divide a cell in Excel based on the number of characters you specify. To make things easier to understand, please consider the following example.

Supposing, you have Product IDs and Product names in one column and you want to extract the IDs into a separate column: Product IDs and Product names to be separated into 2 columns

Since all of the product IDs contain 9 characters, the Fixed width option fits perfectly for the job:

  1. Start the Convert Text to Columns wizard as explained in the above example. In the first step of the wizard, choose Fixed width and click Next. Choose Fixed width and click Next.
  2. Set the width of each column by using the Data preview section. As shown in the screenshot below, a vertical line represents a column break, and to create a new break line, you simply click at the desired position (9 characters in our case): Set the columns' widths using the data preview section. To remove the break, double-click a line; to move a break in another position, simply drag the line with the mouse.
  3. In the next step, choose the data format and destination for the split cells exactly as we did in the previous example, and click the Finish button to complete the separation.

How to separate cells Excel with Flash Fill

Beginning with Excel 2013, you can make use of the Flash Fill feature that can not only automatically populate cells with data, but also split cell contents.

Let's take a column of data from our first example and see how Excel's Flash Fill can help us split a cell in half:

  1. Insert a new column next to the column with the original data and type the desired part of the text in the first cell (participant name in this example).
  2. Type the text in a couple more cells. As soon as Excel senses a pattern, it will populate similar data into other cells automatically. In our case, it's taken 3 cells for Excel to figure out a pattern: Separate cells in Excel with Flash Fill.
  3. If you are satisfied with what you see, press the Enter key, and all the names will be copied to a separate column at once.

How to split cell in Excel with formulas

Whatever diverse information your cells may contain, a formula to split a cell in Excel boils down to finding a position of the delimiter (comma, space, etc.) and extracting a substring before, after or in-between the delimiters. Generally, you'd use SEARCH or FIND functions to determine the delimiter's location and one of the Text functions (LEFT, RIGHT or MID) to get a substring.

For example, you'd use the following formulas to split data in cell A2 separated with a comma and space (please see the screenshot below):

To extract the name in B2:

=LEFT(A2, SEARCH(",",A2)-1)

Here, the SEARCH function determines the position of a comma in A2, and you subtract 1 from the result, because the comma itself is not expected in the output. The LEFT function extracts that number of characters from the start of the string.

To extract the country in C2:

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

Here, the LEN function calculates the total length of the string, from which you subtract the position of the comma returned by SEARCH. Additionally, you subtract the space character (-1). The difference goes to the 2nd argument RIGHT, so it pulls that many characters from the end of the string.

The result will look as follows: DSplit the cell content using formulas

If your delimiter is a comma with or without space, you can use the following formula to extract a substring after it (where 1000 is the maximum number of characters to pull):

=TRIM(MID(A2, SEARCH(",", A2)+1, 1000))

As you see, there is no universal formula that could handle all kinds of strings. In each particular case, you will have to work out your own solution.

The good news is that the dynamic array functions that appeared in Excel 365 make the use of many old formulas unnecessary. Instead, you can use these functions:

  • TEXTSPLIT - split strings by any delimiter that you specify.
  • TEXTBEFORE - extract text before a specific character or substring.
  • TEXTAFTER - extract text after a certain character or word.

For more formula examples to divide cells in Excel, please check out the following resources:

Split cells using Split Text feature

Now that you are familiar with the inbuilt features, let me show you an alternative way to divide cells in Excel. I mean the Split Text tool included with our Ultimate Suite for Excel. It can perform the following operations:

  • Split cell by character
  • Split cell by string
  • Split cell by mask (pattern)

For example, splitting the participant details in one cell into several cells can be done in 2 quick steps:

  1. Select the cells you want to separate, and click the Split Text icon on the Ablebits Data tab, in the Text group. The Split Text tool
  2. On the add-in's pane, configure the following options:
    • Select Comma and Space as the delimiters.
    • Select the Treat consecutive delimiters as one check box.
    • Choose Split to columns.
    • Click the Split button.
    Splitting cells by comma and space as the delimiters

Done! Four new columns with the split data are inserted between the original columns, and you only need to give those columns appropriate names: The original cells are split into 4 separate columns.

Tip. To separate a column of names to the first name, last name and middle name, you can use a special Split Names tool.

If you are curious to see the Split Text and Split Names tools in action, we are welcome to use the download link below. I thank you for reading and hope to see you on our blog next week!

Available downloads

Ultimate Suite 14-day fully-functional version (.exe file)

189 comments

  1. Thank You for this information, i am enjoying flash function for my validation purpose and i am enjoying learning new things.

    Thanks again for all the hard work you have done

  2. Hi,
    Is it possible to separate January-Mar to values January, February, March?

    Thanks!

  3. how to spit in one cell name is A

  4. Hi,guys
    can you help me please how to separate a text from the cell
    for example i have a cell 2Aa0;8x2;Fc< and how can i separate the text "Aa" and "Fc" from the cell by a function.

    Thank you

  5. Hi,
    Pls help how to change 20180320 to 20.03.2018 with function concatenate combine with right,mid & left .
    Thanks

    • =RIGHT(A1;2)&"."&MID(A1;5;2)&"."&LEFT(A1;4)

  6. I have a names list of 600 people. Somehow text to column does not work to seperate them, actually it only works for people with 3 names for some reason. please help me. thanks.

    • Hello,

      I'd recommend you to have a look at our Split Names tool that can help you to detach different parts of the names and place them in separate columns. The add-in comes as a part of our Ultimate Suite for Excel. You can download and install the fully functional 14-day trial version of Ultimate Suite using this direct link. After installation, you'll find the Split Names tool in the Transform section under the Ablebits Tools tab.

      If you don't get the result you need, then you can send us a small sample workbook with your source data and the result you want to achieve. We'll look into your task and try to find a better solution.
      Please shorten your table to 10-20 rows / columns and email it to support@ablebits.com. Please also don't forget to include the link to your comment in the email.

      Thank you.

  7. its very helpfull for us lots of time reduced, thank you very much

  8. Is there a way to split up a blank cell more than twice, giving one more than 4 boxes in 1? I need to have around 6 blank boxes in one cell, almost like a mini-table to indicate 6 different numbers in one cell. I have used the format cells function and so far the most I can get is two divisions in one box but I need 3 or 4.

    Thank you for your time.

  9. Dear Friends

    Please help in Excel Sheet

    Cell 1 Cell 2 Answer
    123 456 123456

    Kindly provide Formula

    • Hello,

      If I understand your task correctly, please try to enter the following formula in cell C1:

      =A1&B1

      where cell A1 is “123”, cell B1 is “456”

      Hope it will help you.

  10. Hi Ablebits.com Team,

    first thank you .

    I have different no in column a b c d f with blank cell, wan't arrange all no in one column accept blank cell . Please suggest

  11. Hi,

    How do i split the following cells into separate cells using a formula.
    I have managed to get the far left and right values but I'm struggling to extract the two center values. I'm at a loss with this. Will highly appreciate any help. I know i can use text to columns but this isn't ideal.

    1:1-1-1
    10:2-3-4

    • Hi, James,

      to separate the second value on the left, try this:
      =LEFT(RIGHT(A1,LEN(A1)-FIND(":",A1)),FIND("-",RIGHT(A1,LEN(A1)-FIND(":",A1)))-1)

      to separate the third value, use the following:
      =LEFT(RIGHT(A1,LEN(A1)-FIND("-",A1)),FIND("-",RIGHT(A1,LEN(A1)-FIND("-",A1)))-1)

      You may find these articles helpful:
      LEFT function
      RIGHT function
      FIND function

      • Hi,
        Could you help me to seperate 4, 5 and last value. Thanks in advance

  12. Good day

    How do I split column that has 2 different data, into 2.
    Here is an example :

    RED APPLES 10 KG SOLD
    GREED APPLES 12 KG AVAILABLE
    GREED APPLES 5 KG AVAILABLE
    Green apples 5KG AVAILABLE
    Green apples 7KG SOLD
    Red apples 3KG SOLD
    Green apples 2KG SOLD
    Red apples 9KG AVAILABLE
    Red apples 15KG SOLD

    I want to create excel spreadsheet with 2 separate coulombs for AVAILABLE and SOLD.

    THANKS

    • Good day to you too,

      well, if you want to use the formulas, let's suppose that your data is in Sheet2, column A.

      You could try this formula in Sheet1 column A (A1, and then copy the formula down):
      =IF((ISNUMBER(SEARCH("sold",Sheet2!A1)))=TRUE,Sheet2!A1,"")

      and this in Sheet1 column B:
      =IF((ISNUMBER(SEARCH("available",Sheet2!A1)))=TRUE,Sheet2!A1,"")

      Then you will only need to delete empty cells.
      Hope this helps!

  13. how to creat formila column to text in excel

  14. __xxx
    ___xxx
    ____xxx
    _xxx

    XXXX
    XXXX
    XXXX

  15. hello there,
    I have a text which I paste on excel but the problem is the text contains space in the beginning for example:
    xxxx
    xxxxx
    xxxxxx

    I want it to be like this
    xxxx
    xxxx
    xxxx

    I know text to column with fixed width will fix this but I need a formula to arrange this file to start from the beginning

    best regards,
    ali

  16. How do I split text paragraph content into cells?
    I have the following text (questions and answers) in one cell and would like each question and answer to become it's own cell in a column. Is this possible? Can I for example get the "Project Goal" and its answer into a different cell either to the right or below?

    • PROJECT GOAL?:

    Understand unique factors in shopping for "high consideration" items, across categories.

    Could also hit bundling and DEX / delivery notification issues.

    • WHAT QUESTION(S) ARE YOU TRYING TO ANSWER WITH THIS RESEARCH?:

    How do customers shop for high-consideration items?

    • WHAT IS YOUR TIME FRAME FOR NEEDED RESEARCH?
    [Please include any milestones, launch dates, dependencies.]

    None. Discovery oriented project.

    • PROJECT PRIORITY - LOW, MEDIUM, HIGH?:
    [If high, why?]

    Medium.

    Thanks!!

  17. In One Cell I have putted the 3 dates Together (Eg 03-12-16/02-01-2017/2-12-16) and in another sheet I only want the Date which is the latest What I have to do please Help me out

  18. Dear,

    when I select product name in Colum-A(Product name)and auto data input in Colum-B (Product Rate), Colum-C (Product Part No),

  19. Dear,

    i have a question for excle...
    how can divide a single call into two parts.

    please help

    Thanks

    • Hi Dear use taxt to column formulla

  20. In excel one is A B C D E another is 1 2 3 4 5, now i request u how to seperate lines 1 to 5 are mixed.

    pl. explain in excel sheet.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)