by 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:
- 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).
- 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)
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
- Start number is the position of the first hyphen +1:
- 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.
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:
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:
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:
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:
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.
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:
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:
- 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 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.
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.
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:
As the result, the source phrase 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
Hit the Split button, and you will immediately get the result:
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:
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:
The result will look similar to this:
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)
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)
306 comments
Hi, How do I get the text before the comma?
Ex: Cell A1= My Name, Eddie
Output to Cell B2 = My Name
Outout to Cell B3 = Eddie
I don't want to use the "text to column function" because I'll have to do it manually when a new entry comes in.
Appreciate if you could help to demonstrate a string for this function.
Many thanks in advance
Eddie:
In B2 enter this:
=LEFT(A1,SEARCH(",",A1,1)-1) this will return "My Name"
In B3 enter this:
=RIGHT(A1,SEARCH(",",A1,1)-2) this will return "Eddie".
Hi,
i have values in one column as below.
ABC (1), IJK (20), LMN (15), XYZ (5)
LMN (10), XYZ (25), ABC (15), IJK (3)
XYZ (25), ABC (225)
i want create new column and extract value for ABC as below
1
15
225
I tried using MID function as explained, but i get value as below.
=MID(A2, SEARCH("ABC",A2) + 5, 3)
1)
15)
225
Can you pls help?
Had to use the FIND command to get the last bracket and subtract positions.
This should work for you.
=MID(A1,FIND("ABC",A1)+5,(FIND(")",A1,FIND("ABC",A1)+5))-(FIND("ABC",A1)+5))
Locates the ABC starting character and adds 5 to get to the number
Then second part finds the first ) after the number
Then it subtracts the position of the ) to get the number for the mid statement
Use VALUE to convert this output value to a number if you want to perform sums on it
One of the column has the value "Fri 6pm PT", I need this value to be split into 3 different columns and also need improvisations like once the column is split the "fri" column should be "Friday" and the column with "6pm" should be "6:00 PM". I need the formula on how to change.
How could I split
L18000160290MASS LLC
into
L18000160290 MASS LLC
Basically I want to take the first 12 characters into a separate cell
Keven:
I think this should work.
Where L18000160290MASSLLC is in A1
=LEFT(A1,12)
Hi,
I have figures in a line beak
59/220118
64/080318
64/100418
128/100518
192/110617
In another column I need to add up the numbers before "/". Do you have a formula for that please?
Thanks in advance.
Naveen:
I think the easiest thing to do in a situation like yours is to use the Text-to-Columns tool that is built in to Excel.
Click the Data tab on your ribbon, then choose Text-to-Columns, then click the Delimited button, then choose the Other option and enter a / in that field. You should then see how your data will be separated into two columns. Click Next and the data will be split into two columns beginning with the column the data was originally in. If you want the split data to be put into a different cell then enter that address in the Destination field in the third step. You can also choose not to import the data after the / . In this last step select the column heading that holds the data you don't need and choose the Do Not Import Column.
Click finish and you have your data in its own column ready to sum.
Try using this. If your value is in H3...
=VALUE(LEFT(H3,FIND("/",H3)-1))
Hi
How to split number of live sheet into individual no and add them
Ex: 254.56 into 25456
Add:2+5+4+5+6
I split no but unable add them
Umesh:
In an empty cell enter =Sum(Range Holding Values)
For example if the numbers are in cells B2,C2,D2,E2 and F2 then =SUM(B2:F2)
Be sure to format the cells B2:F2 as number.
Hi can you please help me split 4017.524120.9822..0000.0000.3137 as
4017
524120
9822
0000
0000
3137
Soni:
You can select the cell with the data and then use the Text-to-Columns tool under Data. Use "." as the delimiter and once the data is in separate cells you can copy the cells and paste into a blank cell using the Paste Special/Transpose option.
If you do a lot of this work The AbleBits Ultimate Suite is probably a better option. It has many tool to help work with data. Splitting your type of data into rows is one of the tools.
In order to split string by dash I wouldn't bother to construct different formulas for each part.
I would, rather, use one single (similar) formula for all columns
For example:
Type the following formula into cell B2 and copy-drag it to the right.
=TRIM(MID(SUBSTITUTE("-"&$A2&" ","-",REPT(" ",255)),(COLUMN()-1)*255,255))
----------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL
Thank You!!
This did the trick for the task I was working on. May I ask what the elements of the formula mean?
Hi Svetlana,
I have a worksheet containing a single column as follows:
1 F01K2502F01L1504A23K1165
2 C09J12518B23K524C07C30988C07C31724C07C31734
I would like to split this single column into multiple columns, like:
1 F01K2502 | F01L1504 | A23K1165 (3 columns)
2 C09J12518 | B23K524C07 | C30988C07 | C31724C07 | C31734 (5 columns)
Any advice?
Thanks a lot beforehand.
Hello,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
Hi Svetlana,
Thanks for your help on this. I hope you are still supporting this post.
The left, mid & right functions only work for 3 columns of data within the source cell. My data has 6 columns with 5 delimiters (",").
If I use LEFT it returns the "first column's data",
If I use MID, it returns the "second column's data",
If I use RIGHT, it returns everything to the right in column "3", which is actually Column 3, 4, 5 & 6.
Is there any way to "count" the delimiters, then return the data before/after the specified Xth delimiter? In other words, similar to VLOOKUP where the formula uses "col_index_num" to specify which data is wanted.
Unfortunately, the data I am trying to extract to place in 2 different cells is in the second & fourth columns.
I have isolated below between *** ***
12/15/2017 16:10,***DYSINGER EAST***,23326,***2163.11***,3150,-9999
This is realtime data from .csv url through Data --> Refresh All, so using the Text to Columns feature or add-ins is not possible. Also I am using MS Excel for Mac, and so far cannot get Excel to recognize the "," in the text string as the delimiters to separate the string automatically into proper columns.
Thanks for any help on this!
Hello,
Please try using a VBA macro. We are always ready to help you, but we do not cover the programming area (VBA-related questions).
You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry we can’t assist you better.
Pls see if you can split the following, in Excel-2010 ...
1 apple. 2 orange. 3 pear. 4 banana
[this is all in a single cell]
into ...
column A column B
1 apple
2 orange
3 pear
4 banana
Thank you.
Hello,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
how to split numbers as mentioned below?
999999999.99 to 99 99 99 999.99;
99999999.99 to 9 99 99 999.99;
9999999.99 to 99 99 999.99;
999999.99 to 9 99 999.99.
and so on.
Thanks in Advance.
Hello,
If I understand your task correctly, please try the following formula:
=SUBSTITUTE(TEXT(A1,"#,##0.00"),","," ")
Hope this will work for you
i want to spit one cell number 12345
My electrical engineer son-in-law is giving us a hint as to what he and my daughter are naming my grandson-to-be. Here is the strand he gave us ^[B-P][a,e,l,o,u]\w{3,} . He then added, "It's a string search pattern. It can be reverse engineered. Can anybody solve this for me?
Hi,
Can you help me splitting the word astro.forumattivo.com to astro and forumattivo.com
thanks!
skuty
08121804902
hi there,
081317003321
081315004023
08121002069
08121803616
08121001992
081316000612
081316002389
08129000777
08121002877
can i ask help how to split between 12 digits & 11 digits different rows?
Use LEN to return the length and test on that.
=If(len(a1)>11,a1,"")
Hi,
Can you please help to seperate only the colours from the cell into different column.
For eg.
From PVC SLEEV 65X6 HEAVY DUTY BLACK COLOUR.Only the Black into different column.
Like wise in given below contents:-
PVC SLEEV 65X6 HEAVY DUTY BLACK COLOUR.
PVC CISTERN WHITE
PVC CISTERN-WHITE
PVC CISTERN -WHITE
Is it possible to slice Inputsome into various cells(Crore, Lac, Thousand, hundreds)?
Inputsome Crore Lac Thousand Hundred
1091050320 9 10 50 320
173387930 17 33 87 930
720333 7 20 333
1209225 12 9 225
3209 3 209
16305 16 305
502 502
50 50
9 9
Hi...
I am trying to use the split function in an Excel 2013 VBA script. However, the function name ('Split') is not highlighted in the editor, and I get a compile error "Cannot return array", so I suspect that the function has been removed.
Could you please confirm that this is the case, or put me right if it isn't? I prefer not to have to write this function myself.. :)
All the best, and thanks fo royur time,
HTML 5 See 15 endorsements for HTML 515
Unity3D See 11 endorsements for Unity3D11
How to split this 15 and 11 in this line.
** number after "see" only i want.