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. Hi

    I would like to take a string of numbers in one cell and separate it by "-". The original data came from a string of columns, which I concat into one cell.
    Orignal data:
    705000 336 10 01 000 0000 35500
    Used Concatenate for the result below
    7050003361001000000035500
    want it to be
    705000-336-10-01-000-0000-335500

    • Hello Shamrock!
      Please try the following formula:
      =SUBSTITUTE(A1," ","-")
      or
      =SUBSTITUTE("705000 336 10 01 000 0000 35500"," ","-")
      Hope you’ll find this information helpful.

    • Instead of concatenating the number and then splitting it back out ...
      Use a1 & "-" & b1 & "-" & "c1 to concatenate with the -'s already in place.

  2. Hello Sir,

    I have data '1234 to 56478' and wanted add comma only for values using excel function and data should look like '1,234 to 55,678'
    How do I do this?

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

      =LEFT(A1,FIND(" ",A1,1)-4) & ","&MID(A1,FIND(" ",A1,1)-3, LEN(A1) - FIND(" ",A1,1)+1) & ","&RIGHT(A1,3)
      I hope it’ll be helpful.

      • Thank you Alexander, It is working for the data which I had given as example...But comma needs to added for every thousands for example for data like '1020000 to 4080000' should be coming as '1,020,000 to 4,080,000' which is not coming now....and also I have the data like'0 to 1360000' and '1360000 +' where this formula is not working.
        Can you help me out with this?

        • I have bunch of excel data which looks like:
          1234 to 56478 (Given formula is working here)
          0 to 2450000
          1090000 to 4080000
          1360000 +
          1234 to 56478

          • Hello Santhosh!
            Please use the following formula
            =TEXT(LEFT(A1,FIND(" ",A1,1)-1),"#,##0") & MID(A1,FIND(" ",A1,1), FIND(" ",A1,FIND(" ",A1,1)+1) - FIND(" ",A1,1)+1)&TEXT(RIGHT(A1,LEN(A1) - FIND(" ",A1,FIND(" ",A1,1)+1)),"#,##0")
            If there is anything else I can help you with, please let me know.

  3. Dear madam,

    you explain it in detail but i am unable to get the function =cocatenate(A1,",",B1) using text1 and text 2 for exporting it to autocad. please guide me
    Your sincerly
    Satya prakash tiwari
    New Delhi

    • Hello!
      Please specify what is the format of the file you're exporting in autocad (.csv/.txt/.exl). Maybe it'll help to conver all the functions into values beforehand. If you describe your task in more detail, I'll try to find a solution for you.

  4. How can I remove http:// from a website string?

  5. Dear Sir,
    what is the formula of pick the number only.
    Ex- Abhijeet Kumar Singh-7678154068 ASDC
    only I have need of contact number
    Thanks

  6. I need a formula for to split:
    1/03/2019 12:31:27 PM
    to make it read
    01/03/2019 in one column and 12:31 PM in another column
    Thank you!

    • If your data is in A1, in B1 put =INT(A1). Format it as date short. In C1 put =A1-B1 and format it as Time

      Saiph

  7. Need formula for combine two decimal data.
    Example format
    data1 = 0x1000
    data2 = 0x20
    data3 = data1 | data2
    data3 = 0x1020

  8. 2 20/02/20 10:18:11.762 18 225 115 239 35 176 1 0 0 219 0 10 3 143 0 0 23 41 143 62 52 11 197 3 33 217 28 0 4 0 6 101 1 7 14 13 195 2 153 20 23 0 1 14 0 0 5 40 28 12 82 12 215 3 3 255 249 0 0 0 0 205 12 140 36 56 10 246 3 1 0 0 0 0 205 228 71 41 181 12 249 2 252 31 20 0 0 0 255 5 66 24 46 157
    From above string
    I need to get 145th to 149th decimal value. How would i get it.

  9. I have strings of numbers e.g S-N123-AB-FG. I wish to strip the number down to S-N123
    Can anyone assist me please?
    Best Regards
    Ed

  10. I've been using these page as a reference for over a year to help cleanup my website downloads. It has been very helpful. Thanks. QUESTION-

    For the life of me, I can't figure out how to save my own formula and cut and paste it into my excel spreadsheets. Whenever I do, Excel rejects the formula; however, If I hand type it each time, it is fine. If I copy these formulas from your website and paste them into excel and manually change them to suite my needs, it's fine. Just trying to figure out how to make this easier on me. I'd like to set up a macro, but the formulas keep getting rejected unless I manually hand type them each time.... very time consuming. Below are how my formulas should read. Any ideas?

    =LEFT(L2, SEARCH(“]”,L2,1)-1)

    =MID(L2, SEARCH(“]”,L2) + 1, SEARCH(“]”,L2,SEARCH(“]”,L2)+1) - SEARCH(“]”,L2) - 1)

    =RIGHT(L2,LEN(L2) - SEARCH(“]”, L2, SEARCH(“]”, L2) + 1))

  11. I have to split 40k cells data
    In this coloumn of 40 thousand cells, multiple rows contains data that is partially duplicate
    Like
    Fruit orange big
    Fruit orange small
    Fruit orange tiny
    Vegetable tomoto
    Vegetable potato
    Dairy product organic milk
    Dairy product organic cheese
    Dairy product organic cream

    The cell should split once the content of the cell changes

  12. Hello I have been tasked with a project at work.

    I am trying to convert the following
    7' 9 3/16"
    7 9/16"
    7"
    8 3/8"
    8' 2 15/16"
    8' 2 7/16"

    some of these work using this formula:
    LEFT(B34,FIND("'",B34)-1)*12+SUBSTITUTE(MID(B34,FIND("'",B34)+1,LEN(B34)),"""","")

    some of them pop up with a VALUE Error, I presume because the format is wrong some are just inches some are just feet and some are feet and inches. I am trying to find the right formula to help solve this issue. I am sure I will need to add some IFERRORS, or something. I could really use all the help I can get.

    Thanks in advanced!

  13. Hi.... i have the data in B2 like this (CUROFF : OTHAKALMANDAPAM POLLACHI MAIN ROAD,CONTACT DETAILS. : MOB : 6379745419,OFFRES : NO 70 ANJENEYAR COLONY ONDIPUDUR COIMBATORE SOUTH,CONTACT DETAILS. : MOB : 9894144376,CURRES : 48 MANOJ NAGAR KALANGAL ROAD KANNAMPALAYAM,CONTACT DETAILS. : MOB : 6379745419,PERMNENT : 48 MANOJ NAGAR KALANGAL ROAD KANNAMPALAYAM,CONTACT DETAILS. : MOB : 6379745419).... i need to split cell based on "CUROFF:" in c2, "OFFRES :" in D2, "CURRES :" in E2, "PERMNENT :" in G2.... can you please help me on this..... thanx in advance

  14. Please guys I need a very urgent help, I have a data set like
    356897609864
    376554333890
    225657755443
    26H676889378
    ............
    ............
    This numbers are randomly generated using RANDBETWEEN formula.
    I want to break it into 6 up and 6 down in the same cell

  15. I Have data in a cell in this shape
    32202-6536360-9
    I need out put in other cells as one character in each cell
    3
    2
    2
    0
    2
    -
    6
    5
    3
    6
    3
    6
    0
    -
    9
    Is there any solution.

  16. Faculty 9-Over-9 Program: Ahn, Charles H; (45212); GR100000 Payroll
    -I am trying to separate the "Ahn, charles H" and input it into a different cell.

    Thank you in advance for your help!!

  17. A0b1C2D3E4F5G6H7I8J9K
    ff5ff5s6s4sfd5
    sfdfd4465ss4ss5
    How to separate Numbers and alphabets

  18. Hello,
    I need to extract and put in different columns just the PO# (PO+digits) of the below array of strings. Sometimes it is separated by a "," or a " ". And the number of digit of a PO can Vary.
    PurchOrd-PO7825,PurchOrd-PO8037,PurchOrd-PO8095 PurchOrd-PO7920,PurchOrd-PO8025 PurchOrd-PO7616,PurchOrd-PO7786,PurchOrd-PO7797.1,PurchOrd-PO7843,PurchOrd-PO7986

    Result expected : PO7825 | PO8037|...| PO7797.1

    You would be such a hero if you could help me on this :)

    Thank you very much in advance (with or without answer!)

  19. DATA 1
    "Interscience Sdn Bhd
    2, Jalan Sg. Kayu Ara 32/38
    Berjaya Industrial Park
    40460SHAH ALAM
    SELANGOR"

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

    output: Selangor

  20. Hi,
    I want to split the below text:
    INSP INSPIRIT ENERGY HOLDINGS PLC ORD 0.001P
    To be:
    First column: INSP
    Second Column: INSPIRIT ENERGY HOLDINGS PLC ORD 0.001P

    briefly, i want to split the text after the first space and the rest of the text to be added in the next column.

    Thanks

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