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)

Table of contents

189 comments

  1. Hi Svetlana,

    I have a problem. in one cell i have entered a text, i need to insert a text from another sheet in between.

    data in cell "( City/Centre:__________________)"

    now i want to insert from another sheet a text data or a numeric data which should be as shown below.

    Data in cell after inserting "(Centre / City :_________Bangalore____)"

    The word bangalore should also be underlined.

    Can this be done. if so how to do it. it will be very helpfull in my work.

    Thanks

    Chandra shekar

  2. how to insert cell within a cell

  3. How to remove 91 from phone list . see below details for your references

    Phone#
    91 281 3018308
    91 0281 3018227
    91 0281 3018325
    91 812 87482

  4. Sir,
    Thank you very much for this Fantastic tutorial,.
    Looking forward for your help to resolve the following.
    I have a table as attached below
    3043 ISACN-194 ISA MATHEW PV
    PARASSERIL, KUNNUMKAI, WEST ELERI
    BEEMANADI
    3044 ISACN-195 ISA SREEDHARAN K
    POYYALAM HOUSE, ENNAPARA, KANHANGAD
    ATHIRA TEXTILES, ENNAPARA
    3045 ISACN-196 ISA BABY JOSEPH
    THEKKINIKUNNEL, POODAMKALLU, KASARAGOD
    CHEERS COOL BAR, POODAMKALLU
    3046 ISACN-197 ISA ABBAS
    ORAVANKARA, ERAVANNUR, CALICUT
    HAPPTY BOOK CENTER, BEKAL, KANHANGAD

    in each serial no, the address field is in three rows in single colmn. I have to get this adress column to be split in three seperate columns. means 1,4,7,10 etc in a col and 2,5,8,11 etc in another coloumn and 3,6,9 12 etc in another one.
    pls give a formulae for this.
    thank u

  5. Your article is fantastic - however - I seem to have a bit of a different issue - not sure if it can be done or not
    I have a txt file, opened in excel, and I was able to create 2 columns from 1 so that I have a start to what I want to accomplish:
    COL A COL B
    BEGIN VEVENT
    DTSTART 2010103108000
    DTEND 2010103109000
    DESCRIPTION FIRSTNAME LASTNAME
    END VEVENT
    and the pattern repeats

    Is there anyway I can create separate columns labeled BEGIN DTSTART DTEND DESCRIPTION (from column A)
    and have the info from Column B then fill in the appropriate column?

  6. Hi,

    Having looked through how you have solved so many others problems I hope you can help me!

    I have been importing a table from a website and written quite a bit of excel logic after that import. Problem is the website has now changed and is now no longer an importable table!

    This means the data input from the website now comes into 1 cell of data in one string.

    The data is delimited by an increasing number i.e. 1ChrisCatling2VickyCatling3JamesCatling...
    Can I use the Text to Columns wizard by specifying a number (which is obviously changing) so for example to display
    1 Chris Catling
    2 Vicky Catling
    3 James Catling
    ...

    • Hello Chris,

      Try to open the website using another browser, it may help. If it doesn't, then you need a small VBA macro, formulas and the Text to Columns feature cannot help you with your task.

  7. i have huge data were different format with the combination of alfanumrci samle as below just want sapration these data

    NEFT CRDEUT0784BBYIDEA CELLULAR LTD
    NEFT CRCITI0000006GOOGLE INDIA PRIVATE
    NEFT CRCITI0000004IBM INDIA PVT LTDCA

    Can you help me on this

  8. Hi,

    What a fantastic article, very informative and well written.

    I do have a problem with the text to columns feature; when splitting a column containing numbers with leading zero's, they zero's are removed in the final output columns.

    For example 'ABC.005.001' is split to 'ABC', '5', '1'. I am missing the leading zeros, ideally the output should be 'ABC', '005', '001'.

    I have tried setting the columns to text format but still no luck!

    Regards

    Shane

    • Hi Shane,

      Thank you for your kind words, Shane.

      It looks strange, I tried to reproduce the issue using your example, everything worked correctly in Excel 2013. Please try to set the format of the columns where you will insert data as Text beforehand, and also set the Text format for all columns in the "Text to columns" wizard.

      If this doesn't work for you, please send a sample workbook with your data to support@ablebits.com and we will try to help.

  9. Hello,

    Thank you for this article, in which a deep understanding and diligent attitude can be shown.

    I got, however, a problem, that remains unsolved (or I overlooked the solution), though it seems to be quite simple, or at least can be well defined. It goes like this:

    There is a column (let's say column A), containing some technical descriptions, no more then 100 characters long, but some of these descriptions can even be as short as only one word. Just a common characters collection, like alpha, numbers, slashes, dashes, spaces etc. Nothing special there. What I need to do is to split it into 3 columns (say columns B,C,D) of no more then 35 characters each; (content of these columns will then serve another application as subsequent lines of descriptions). Up to now it's pretty simple, but I don't like to have it split in a way that words are cut in half, but rather split it at where spaces can be found. So we can define rules (just for the 1st splitting, for subsequent could be easily transposed):
    1. splitting should occur at space directly before 36th character in column A,
    2. if there is no space between, let's say, 20th and 36th character in column A, splitting should occur after 35th character,
    3. if there's less then 35 characters in column A, then column B = column A, column C is just empty (I mean no things like #ARG!, #VALUE! etc.)

    I would be grateful if you could direct me towards the proper attitude to such a task.

    Thank you again,
    Mario

    • Hello Mario,

      I think this task is too complex to be handled by formulas. Anyway, very long and complicated formulas will be required, so I'd opt for a macro instead.

  10. Hi,

    I am trying to split cells in excel. The tricky part is that I need to split differently per line. See example below.

    Line 1 should be split this way:
    XXECR06541200960635204109DDDD

    x xe cr 0654 120096 0635 204109 DDDD

    Line 2 should be split this way:
    PP6SCR06511222960022204109EEEE

    p p6s cr 0651 122296 0022 204109 EEEE

    I would truly appreciate any help here.

    thanks.

    • Hello Martin,

      If you need to split the odd lines in one way, and the even lines in the other, you can do the following:

      1. Insert two helper columns into your table. Let's name the 1st column – ID, the 2nd – ODD.

      Type in 1 in the first cell and 2 in the second cell of the ID column. Select both cells and drag the fill handle (a small black square in the bottom right corner of the selected cell) down to auto fill the rest of the column with consecutive numbers 1,2,3,4 etc. For more details about using Excel's AutoFill feature, please see this article:
      https://www.ablebits.com/office-addins-blog/autofill-excel/

      2. In the first cell of the ODD column enter the formula =mod(F2,2), where F2 is the first cell in the ID column.

      3. Copy the formula to the other cells in your table, see here for a quick way:

      4. Sort your table first by the ODD column, then by the ID column.

      5. Split the upper part of your table in one way, the lower part in the other one.

      6. Sort the original table + the results of splitting by the ID column. As a result, you'll get the table where the rows are the same as in the original table.

      7. Delete the helper columns.

      Hope this is the result you are looking for.

  11. I have a question on my data set. I have a bunch of addresses in a data file, but these addresses come from another data source and have been split by an enter (alt-enter in excel). I am looking for a way to split the data based on this enter and have been unsuccessful thusfar. I hope there is any formula to do this, but I fear I'll have to manually insert a ; everywhere to split it.

    What do I mean:
    In Excel if I enlarge my function box (the one in the top where you insert your data) I can see the address perfectly split by enters having a new line for each part of the adress..
    Example:
    11 Mosside Drive
    Tradespark
    Nairn
    IV12 5PN

    unfortunately in the excel field below this is shown as one large string of text..
    Example:
    "11 Mosside DriveTradesparkNairnIV12 5PN"
    Only showing spaces where there are any in a single line.

    Is there any way I could split this based on the enters given? or is my only option to split this one manually??

    Thanks for the help!
    Jeroen

    • Hello Jeroen,

      No need to split it manually. You can try out his way:

      - Go to Text to columns > Delimited
      - Check the "Other" checkbox, place the mouse pointer in the box next to it and Ctrl+J (it is a keyboard shortcut for a line break).

      You should see the text properly split to columns in the Preview window.

      • thank you so much this worked for me and i didn't have to write 100 number :-)

  12. suraj,kumar,bharti LEFT(A2, FIND(",",A2)-1) , =MID(A2, FIND(",",A2) + 2, FIND(",",A2,FIND(",",A2)+2) - FIND(",",A2) - 2) , =RIGHT(A2,LEN(A2) - FIND(",", A2, FIND(",", A2) + 1))

  13. one row with column data contains sequence 'COI-CLP-COI-FDT988/1200-CLC11200-COU-FD425'
    another row with column data contains 'CLC1200-COU'
    I want this sequence to be split and post under appropriate column
    example :

    Row Sequence COI FDSC CLSC DX F425
    ROW1 COI-FDSC-CLSC-DX-F425-COI-DX 2 1 1 2 1
    ROW2 CLSC-DX-COI 1 1 1

  14. Hello,

    I seem to get an error message when using the formulas =MID(A2, SEARCH(",",A2) + 2, SEARCH(",",A2,SEARCH(",",A2)+2) - SEARCH(",",A2) - 2) and also =RIGHT(A2,LEN(A2) - SEARCH(",", A2, SEARCH(",", A2) + 1)).

    It states there are too many arguments

    • Just to note, I am trying to seperate three words with comma's and no spaces in cell A2. example - Joe,Bloggs,1998

      • Hi Phil,

        Your second formula is correct. But for some reason it contains long dashes instead of the minus sign, and curly quotes instead of smart quotes. This sometimes happens when copying formulas from the web. Just fix this and the formula =RIGHT(A2,LEN(A2) - SEARCH(",", A2, SEARCH(",", A2) + 1)) will work fine.

        As for the first formula, you have to replace +2 with +1 and -2 with -1, because your data does not contain spaces like in my example. So here's the correct formula for your data:
        =MID(A2, SEARCH(",",A2) + 1, SEARCH(",",A2,SEARCH(",",A2)+1) - SEARCH(",",A2) - 1)

        • OMG THANK YOU for the " comments.

  15. sir
    i am unable to do the split cells in open office excel sheet how to do it can you give the examples sir.

    • Hi Chakravarti,

      Sorry I am not able to help with the Open Office, I do not have any experience with it.

  16. This is an excellent article. The best thing are the screenshots that explain all the steps. Thank you very much.

  17. hai shawna

    My name is venkatesh i am working at some office i have problem in excel sheet that is in that excel sheet contain only one cell somany charaters involved in that cell. in the sense a cell contain a,b,c,d, but i need to convert a singlecolumn data in the sense A
    B
    c
    D if you know please help me and give screen shots

    • Hello Venkatesh,

      I do not exactly understand what you are trying to achieve. If you want to replace commas with line breaks, then select all the cells you want to change, press Ctrl+H, enter comma (or semicolon) in the 'Find What' field, put the cursor in the 'Replace With' field and press Ctrl+J.

      If you need to change the lowercase to uppercase, please check out this article .

      • I'm guessing, what he's trying to achieve is to separate characters from 1 cell into several columns.

        Eg.
        abcde (1 cell) -> a | b | c | d | e (5 cells/columns)

        My suggestion:
        LEFT(), RIGHT() functions (with relation to LEN() & SEARCH() function).. Google them.

  18. Hi Your article is great. I'm trying to use this information to split up address but the lenght of the street numbers are names are not uniform. Here is data below. Any idea of what formula i can use to put street number, street name in separate columns?

    Address Street Number Street Name Street Type
    340 Pinemont Rd. Ne
    11414 83 Ave.
    10210 91 St.
    2 Hillman Close
    37 Carswell Rd. S E
    207 10 Ave. SE
    220 3Rd. Ave. W
    4607 45 Ave.
    4425 5 St. E

    • Thank you Shawna!

      Regrettably, I cannot suggest any formula for your case. You can check out the Address Assistant add-in that is especially designed for splitting all kinds of addresses.

    • Use the spaces as delimiters?

  19. Thank you for this article. It helped me a lot in my work. Really appreciate this.

    • Thank you for your comment Karthik! I am really glad to know it was helpful.

  20. We have two excel sheets having salary values with badge id's,both the badge id's contains alpha numeric but numeric. We have to pull the values from sheet #2 to Sheet #1 by using formula,please advise

    Example :
    Sheet#1 : ASDF123 (Badge ID)
    Sheet#2 : FDSA123 (Badge ID)

    • Hello Ashok,

      You can do it in this way:

      - On Sheet#2, create a helper column "Badge ID Helper" and copy this formula across the "Badge ID Helper" column:
      =MID(A2,4,1)&MID(A2,3,1)&MID(A2,2,1)&MID(A2,1,1)&MID(A2,5,4)

      - Merge Sheet #2 and Sheet #1, by matching "Badge ID Helper" and "Badge ID" on sheet#1. You can use our tool, Merge Tables Wizard for easy merging. If you prefer to use Excel formulas, please check out this article - Look up with Lookups in Excel.

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 :)