How to split text string in Excel by comma, space, character or mask

The tutorial explains how to split cells in Excel using formulas and the Split Text feature. You will learn how to separate text by comma, space or any other delimiter, and how to split strings into text and numbers.

Splitting text from one cell into several cells is the task all Excel users are dealing with once in a while. In one of our earlier articles, we discussed how to split cells in Excel using the Text to Column feature and Flash Fill. Today, we are going to take an in-depth look at how you can split strings using formulas and the Split Text tool.

How to split text in Excel using formulas

To split string in Excel, you generally use the LEFT, RIGHT or MID function in combination with either FIND or SEARCH. At first sight, some of the formulas might look complex, but the logic is in fact quite simple, and the following examples will give you some clues.

Split string by comma, semicolon, slash, dash or other delimiter

When splitting cells in Excel, the key is to locate the position of the delimiter within the text string. Depending on your task, this can be done by using either case-insensitive SEARCH or case-sensitive FIND. Once you have the delimiter's position, use the RIGHT, LEFT or MID function to extract the corresponding part of the text string. For better understanding, let's consider the following example.

Supposing you have a list of SKUs of the Item-Color-Size pattern, and you want to split the column into 3 separate columns:
Splitting the source text strings into 3 columns

  1. To extract the item name (all characters before the 1st hyphen), insert the following formula in B2, and then copy it down the column:

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

    In this formula, SEARCH determines the position of the 1st hyphen ("-") in the string, and the LEFT function extracts all the characters left to it (you subtract 1 from the hyphen's position because you don't want to extract the hyphen itself).
    Use the LEFT function to extract the characters before the first hyphen.

  2. To extract the color (all characters between the 1st and 2nd hyphens), enter the following formula in C2, and then copy it down to other cells:

    =MID(A2, SEARCH("-",A2) + 1, SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1)
    Use the MID function to extract the characters between the 2nd and 3rd hyphens.

    In this formula, we are using the Excel MID function to extract text from A2.

    The starting position and the number of characters to be extracted are calculated with the help of 4 different SEARCH functions:

    • Start number is the position of the first hyphen +1:

      SEARCH("-",A2) + 1

    • Number of characters to extract: the difference between the position of the 2nd hyphen and the 1st hyphen, minus 1:

      SEARCH("-", A2, SEARCH("-",A2)+1) - SEARCH("-",A2) -1

  3. To extract the size (all characters after the 3rd hyphen), enter the following formula in D2:

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

    In this formula, the LEN function returns the total length of the string, from which you subtract the position of the 2nd hyphen. The difference is the number of characters after the 2nd hyphen, and the RIGHT function extracts them.
    Use the RIGHT function to extract the characters after the 3rd hyphen.

In a similar fashion, you can split column by any other character. All you have to do is to replace "-" with the required delimiter, for example space (" "), comma (","), slash ("/"), colon (";"), semicolon (";"), and so on.

Tip. In the above formulas, +1 and -1 correspond to the number of characters in the delimiter. In this example, it's a hyphen (1 character). If your delimiter consists of 2 characters, e.g. a comma and a space, then supply only the comma (",") to the SEARCH function, and use +2 and -2 instead of +1 and -1.

How to split string by line break in Excel

To split text by space, use formulas similar to the ones demonstrated in the previous example. The only difference is that you will need the CHAR function to supply the line break character since you cannot type it directly in the formula.

Supposing, the cells you want to split look similar to this:
The cells to be split by spaces.

Take the formulas from the previous example and replace a hyphen ("-") with CHAR(10) where 10 is the ASCII code for Line feed.

  • To extract the item name:

    =LEFT(A2, SEARCH(CHAR(10),A2,1)-1)

  • To extract the color:

    =MID(A2, SEARCH(CHAR(10),A2) + 1, SEARCH(CHAR(10),A2,SEARCH(CHAR(10),A2)+1) - SEARCH(CHAR(10),A2) - 1)

  • To extract the size:

    =RIGHT(A2,LEN(A2) - SEARCH(CHAR(10), A2, SEARCH(CHAR(10), A2) + 1))

And this is how the result looks like:
Splitting strings by the space character.

How to split text and numbers in Excel

To begin with, there is no universal solution that would work for all alphanumeric strings. Which formula to use depends on the particular string pattern. Below you will find the formulas for the two common scenarios.

Split string of 'text + number' pattern

Supposing, you have a column of strings with text and numbers combined, where a number always follows text. You want to break the original strings so that the text and numbers appear in separate cells, like this:
Splitting an alphanumeric string to text and number

The result may be achieved in two different ways.

Method 1: Count digits and extract that many chars

The easiest way to split text string where number comes after text is this:

To extract numbers, you search the string for every possible number from 0 to 9, get the numbers total, and return that many characters from the end of the string.

With the original string in A2, the formula goes as follows:

=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))

To extract text, you calculate how many text characters the string contains by subtracting the number of extracted digits (C2) from the total length of the original string in A2. After that, you use the LEFT function to return that many characters from the beginning of the string.

=LEFT(A2,LEN(A2)-LEN(C2))

Where A2 is the original string, and C2 is the extracted number, as shown in the screenshot:
A column of strings is split to text and numbers

Method 2: Find out the position of the 1st digit in a string

An alternative solution would be using the following formula to determine the position of the first digit in the string:

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))

Once the position of the first digit is found, you can split text and numbers by using very simple LEFT and RIGHT formulas.

To extract text:

=LEFT(A2, B2-1)

To extract number:

=RIGHT(A2, LEN(A2)-B2+1)

Where A2 is the original string, and B2 is the position of the first number.
Another way to split 'text-number' strings

To get rid of the helper column holding the position of the first digit, you can embed the MIN formula into the LEFT and RIGHT functions:

Formula to extract text:

=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

Formula to extract numbers:

=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)

Split string of 'number + text' pattern

If you are splitting cells where text appears after number, you can extract numbers with the following formula:

=LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, ""))))

The formula is similar to the one discussed in the previous example, except that you use the LEFT function instead of RIGHT to get the number from the left side of the string.

Once you have the numbers, extract text by subtracting the number of digits from the total length of the original string:

=RIGHT(A2,LEN(A2)-LEN(B2))

Where A2 is the original string and B2 is the extracted number, as shown in the screenshot below:
Splitting a column of strings where numbers appear before text

Tip. To get number from any position in a text string, use either this formula or the Extract tool. Or you can create a custom function to split numbers and text into separate columns.

This is how you can split strings in Excel using different combinations of different functions. As you see, the formulas are far from obvious, so you may want to download the sample Excel Split Cells workbook to examine them closer.

If figuring out the arcane twists of Excel formulas is not your favorite occupation, you may like the visual method to split cells in Excel, which is demonstrated in the next part of this tutorial.

How to split cells in Excel with Split Text tool

An alternative way to split a column in Excel is using the Split Text feature included with our Ultimate Suite for Excel, which provides the following options:

To make things clearer, let's have a closer look at each option, one at a time.

Split cells by character

Choose this option whenever you want to split the cell contents at each occurrence of the specified character.

For this example, let's the take the strings of the Item-Color-Size pattern that we used in the first part of this tutorial. As you may remember, we separated them into 3 different columns using 3 different formulas. And here's how you can achieve the same result in 2 quick steps:

  1. Assuming you have Ultimate Suite installed, select the cells to split, and click the Split Text icon on the Ablebits Data tab.
    The Split Text option
  2. The Split Text pane will open on the right side of your Excel window, and you do the following:
    • Expand the Split by character group, and select one of the predefined delimiters or type any other character in the Custom box.
    • Choose whether to split cells to columns or rows.
    • Review the result under the Preview section, and click the Split button.

Splitting a string by the specified character

Tip. If there might be several successive delimiters in a cell (for example, more than one space character), select the Treat consecutive delimiters as one box.

Done! The task that required 3 formulas and 5 different functions now only takes a couple of seconds and a button click.
A column of strings is split into 3 different cells in a button click.

Split cells by string

This option lets you split strings using any combination of characters as a delimiter. Technically, you split a string into parts by using one or several different substrings as the boundaries of each part.

For example, to split a sentence by the conjunctions "and" and "or", expand the Split by strings group, and enter the delimiter strings, one per line:
Splitting cells by strings

As the result, the source phrase is separated at each occurrence of each delimiter:
The source string is separated at each occurrence of each delimiter

Tip. The characters "or" as well as "and" can often be part of words like "orange" or "Andalusia", so be sure to type a space before and after and and or to prevent splitting words.

And here another, real-life example. Supposing you've imported a column of dates from an external source, which look as follows:

5.1.2016 12:20
5.2.2016 14:50

This format is not conventional for Excel, and therefore none of the Date functions would recognize any of the date or time elements. To split day, month, year, hours and minutes into separate cells, enter the following characters in the Split by strings box:

  • Dot (.) to separate day, month, and year
  • Colon (:) to separate hours and minutes
  • Space to separate date and time

Splitting strings by 3 different delimiters

Hit the Split button, and you will immediately get the result:
Day, month, year, hours and minutes appear in separate cells

Split cells by mask (pattern)

Separating a cell by mask means splitting a string based on a pattern.

This option comes in very handy when you need to split a list of homogeneous strings into some elements, or substrings. The complication is that the source text cannot be split at each occurrence of a given delimiter, only at some specific occurrence(s). The following example will make things easier to understand.

Supposing you have a list of strings extracted from some log file:
A list of strings to be split by pattern

What you want is to have date and time, if any, error code and exception details in 3 separate columns. You cannot utilize a space as the delimiter because there are spaces between date and time, which should appear in one column, and there are spaces within the exception text, which should also appear in one column.

The solution is splitting a string by the following mask: *ERROR:*Exception:*

Where the asterisk (*) represents any number of characters.

The colons (:) are included in the delimiters because we don't want them to appear in the resulting cells.

And now, expand the Split by mask section on the Split Text pane, type the mask in the Enter delimiters box, and click Split:
Splitting strings by mask

The result will look similar to this:
The original strings are split into 3 columns.

Note. Splitting string by mask is case-sensitive. So, be sure to type the characters in the mask exactly as they appear in the source strings.

A big advantage of this method is flexibility. For example, if all of the original strings have date and time values, and you want them to appear in different columns, use this mask:

* *ERROR:*Exception:*

Translated into plain English, the mask instructs the add-in to divide the original strings into 4 parts:

  • All characters before the 1st space found within the string (date)
  • Characters between the 1st space and the word ERROR: (time)
  • Text between ERROR: and Exception: (error code)
  • Everything that comes after Exception: (exception text)

Splitting a column of strings into 4 columns

I hope you liked this quick and straightforward way to split strings in Excel. If you are curious to give it a try, an evaluation version is available for download below. I thank you for reading and hope to see you on our blog next week!

Available downloads

Excel Split Cells formulas (.xlsx file)
Ultimate Suite 14-day fully-functional version (.exe file)

Table of contents

306 comments

  1. Hello, this is so helpful thanks.
    I do need help on how to separate this to display what is on the right.
    Anna Clarence - 078933 Marcus Stuart - 05674 ==> Anna Clarence - 078933
    ==> Marcus Stuart - 05674
    Patrick Wine - 0845 Oscar Lutherwood - 06522 ==> Patrick Wine - 0845
    ==>Lutherwood - 06522
    Thanks!

    • Hi!
      To split text on the fourth space, you can use the formulas:

      =LEFT(A1,SEARCH("#",SUBSTITUTE(A1," ","#",4),1)-1)
      =MID(A1,SEARCH("#",SUBSTITUTE(A1," ","#",4),1)+1,50)

      I hope I answered your question.

  2. I have a requirement to split one column data(say Column E) separated by ". "(dot and space) into new rows depends on quantity given in next column (say Column F). Also i need the same cell content of the splitted row for the all newly created Rows.

    Input format :
    CAPACITOR (in Column D) --> C1. C2.(in column E) --> 2 (in column F --> Kemet (in column G)

    Expected result:
    CAPACITOR (in Column D) --> C1(in column E) --> 2 (in column F --> Kemet (in column G)
    CAPACITOR (in Column D) --> C2(in column E) --> 2 (in column F --> Kemet (in column G)

    Thanks for the support

  3. There is never a solution to split a text string with 4 sections into 4 seperate columns. I cannot use a text to columns approach either. I must take a string of data (1234567>xx-093456>DesignedBy>Weller&Sons). This information must be split into 4 separate columns on the worksheet. I can figure out how to use LEFT and MiD but the RIGHT never works. I cannot find a solution to break out the "Weller&Sons" no matter how hard I try. This is I am sure very simple for smarter excel users - but for me, I am manually typing hundreds of records a day, when I am certain there must be a trick that is not published. Seems most splits are with text to columns (I cannot use) and only for names (first and last). So, is there a solution out there that has not yet be developed or published, or is this an impossible task that will require me to carry on with the retyping of that last data point. Each day there are 100 more records to export and add details for. Thanks for any suggestions and aid.

  4. Good Day,

    How to seperate this

    METS$P0072;;HVA0302221;1;03/02/2022;03/02/2023;1;20.0

    ITEM CODE BATCH MFG DATE EXP DATE QTY KILO
    P0072 HVA0302221 03/02/2022 03/02/2023 1 20

    THANK YOU VERY MUCH

      • Sorry I tried many times but failed to do so. I am asking for your help. This will be a great help for my report.

        METS$P0072;;HVA0302221;1;03/02/2022;03/02/2023;1;20.0

        This will be split into this

        ITEM CODE
        P0072

        BATCH
        HVA0302221

        MFG DATE
        03/02/2022

        EXP DATE
        03/02/2023

        QTY
        1

        KILO
        20

        THANK YOU VERY MUCH.LOOKNIG FORWARD HEARING YOU SOON

  5. Hello,

    I have a text string which i'd like to split by character at either "K" or "L". I've got the basic formula to split at one character, but I can't work out how to look for either a K or an L and then split the text there.

    This is the formula I am using: =RIGHT(G13,LEN(G13)-FIND("K",G13))
    These are examples of the text:
    MC - K07.01
    Suite I Water Inorganics Etc - L03.17

    • ** The text I am looking to split would be like this:

      MC - K07.01 to split "K07.01"
      Etc - L03.17 to split "L03.17"

    • Hello!
      If I understand your task correctly, the following formula should work for you:

      =RIGHT(G13,LEN(G13)- (IFERROR(FIND("K",G13),1)*IFERROR(FIND("L",G13),1)))

      I hope it’ll be helpful.

      • Thank you Alexander, that works well! I am also trying to retain the "K" or "L", is that something which is possible?

  6. I have below alpha numeric line in A1 cell. I need sum of numeric from from space 27 i.e. 276 to /ON. Can you pls help?

    XX¥XXXX111111/ .00/ 276.00/ 79.25/D43.38/D3.49/ON

  7. I need to separate a string into 3 parts: 1-2 Prefix letters, number(1-3 digits) and a suffix letter. Every string may contain 1-4 characters, either just numbers or 1-3 of them.

    String Example and the thing I want it to spit out:
    2 (0,2,0)
    2A (0,2,A)
    46 (0,46,0)
    60X (0,60,X)
    208 (0,208,0)
    234X (0,234,X)
    H2 (H,2,0)
    H1A (H,1,A)
    A20 (A,20,0)
    E21A (E,21,A)
    X970 (X,970,0)
    NA20 (NA,20,0)
    (there is no 2 letter prefix+1 digit number+1 suffix in the strings)

    • Hello!
      The formula below will do the trick for you:

      =IF(ISNUMBER(--LEFT(A1,1)),"0","")& SUBSTITUTE(A1,CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$94),1)),MID(A1,ROW($1:$94),1),"")),"," &CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$94),1)),MID(A1,ROW($1:$94),1),""))&",") &IF(ISNUMBER(--RIGHT(A1,1)),"0","")

  8. Jack ronald::fernando::clovrrse

    How can I split the above one in 3 different columns

  9. One suggestion: the example with the "To extract the color ..." part using MID and Search would be improved if the separator is not a hyphen in both instances. It is hard to tell which reference is to the first instance of a hyphen and which is to the 2nd instance...unless one already knows. Otherwise a really great resource. Thank you!

  10. Hello - Alexander seems to be a great help here with specialized cell date. Alexander - maybe you could help me here?

    I have a cell that can have multiple text values in it split on multiple lines in the cell H:

    "Shift Differential Rate 2, 1.50 USD Hourly
    Shift Differential Rate 3, 2.00 USD Hourly
    Shift Differential Rate 4, 2.00 USD Hourly
    Hourly - Hourly Plan, 15.29 USD Hourly"

    I want to split the string info I, J, K, L

    Additionally there are cells that do not have this text but only this text in Cell H:
    Salary - Salary Plan, 64,480.00 USD Annual

    Any suggestions.
    Thx in advance

  11. Hello, I have a long text in one column and I need to split it into different sections. When I tried to split by all "," "." " " I have over 5000 columns. Anyone who might help ?
    Part of the text in one column:
    Zsolt Geretovszky University of Szeged, Hungary,2,33,16.50.Ziming Zhong 1,56,56.00,Giorgio Maddaluno.Zhixian X Jiu Wuhan Polytechnic University, China,2,4,2.00.

    Wanted result: (bigger spaces = different columns - f.e. Zsolt Geretovszky is name, university of szeged is school etc.)
    Zsolt Geretovszky University of Szeged Hungary 2 33 16.50
    Ziming Zhong 1 56 56.00
    Giorgio Maddaluno
    Zhixian X Jiu Wuhan Polytechnic University China 2 4 2.00

    • Hello!
      Your data doesn't have a pattern. Space is used in the first and last name, and between the first name and the school. The period is used in numbers and to separate records. I cannot help you.

  12. my data in one column:

    GLES2_IMPL_EXPORT extern const char kEnableGpuClientTracing[]
    GPU_EXPORT extern const char kDisableShaderNameHashing[]
    extern const char kNearbyShareHTTPHost[]
    COMPONENT_EXPORT(ASH_CONSTANTS) extern const char kArcDisableAppSync[]

    And i need:- kEnableGpuClientTracing[]
    kDisableShaderNameHashing[]
    kNearbyShareHTTPHost[]
    kArcDisableAppSync[] to be seperated.

    When I'm using the formulae it got separated with "extern const char kDisable....."
    could you help me through this?

  13. I have a comma delimited list of zip codes that I need split into 1200 character max lengths while still keeping the zip codes together, but not going over 1200 characters. Is there any formula that will do that so I don't have to manually do it?

    Thanks!

    • Just to clarify - they are all in one cell and it is 8747 characters in one cell. I'd like to have cells that are no more than 1200 characters in length, without breaking apart the zip codes. Thanks!

  14. Hi,
    I would like to split an 11 digits sequence e.g. 23456543987 into 11 separate cells in excel.
    Can you please advise me?
    thanks a lot.

      • thank you Alexander

  15. Go to Data > Text to Columns and set delimiter to -

  16. Hi,
    In cells of Column A I have data:
    55771 13 units
    44231 5 units
    12341 22 units
    and etc....
    In adjacent cells of Column B I need:
    55771
    44231
    12341
    In adjacent cells of Column C:
    13 units
    5 units
    22 units
    Is it possible with formula?

      • Is there any chance to do it with one formula?

        • Hello!
          The formula changes the value only in the cell in which it is written. You have 2 columns, so you need 2 formulas. Or write a VBA macro.

          • Ok, Thank you

  17. Hi, please help me out.

    Column A has,
    7000
    12345
    54543
    7890

    it has to be separated into two units from the last and to be written in columns B, C and D... like...

    In Column B
    00
    45
    43
    90

    In column C
    70
    23
    45
    78

    In column D
    0
    1
    5
    0

    (zero- since there are no more digits there)

    Please help me with the formula.
    Thanks.

  18. Cell has string of 6 digit numbers: A1=123456 234567 345678 456789 ... Split the string of 6-digit numbers into single column, B1=123456, B2=234567, B3=345678, B4=456789, .... Any suggested solution? Thank you.

    • Try using:
      =MID($A$1,IF(ROW(A1)>1,(ROW(A1)-1)*6+1,ROW(A1)),6)
      This will read the row the formula is on and give you the 6 characters from that row's set
      Drag the formula down for as many rows as there are data sets in cell A1

  19. Hi, My query is that if we have first names and last names combined in a column (with both of them starting with capital letters) then is it possible to separate the strings into first name and last name? both the first and last names have different number of characters.

  20. 012-01-I-020-RUBBER RUNNER SET

    I WANT TO SPLIT ABOVE DATA AS BELOW
    012-01-I-020 IN ONE CELL AND RUBBER RUNNER SET IN ANOTHER CELL

    my data actual data as below which i want to split.
    012-01-I-020-RUBBER RUNNER SET
    01-U-428-UNWINDER UN SCREW AND FLANGE ASSY M104
    02-P-458-FR 59-48 1080 28 1334 MS/MS 104
    03-C-096-CUTTING BRIDGE ASM FOR Ø130ROTARY(M-104)
    03-C-4016-GFR 44-32 360 25 386 MS/MS 104
    09-01-S-142-TOP & BOTTOM KNIFE BRKT-L.H 60MM WIDTH

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