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
Y:O10111:5242045056:PPX :713907770199
how to split in 4 column with removal of colon with formula.
ex- 1st column=Y
2ND COLUMN=O10111
3 RD COLUMN=5242045056
4TH COLUMN=PPX
5TH COLUMN=713907770199 WITH EXCELL FORMULA.
How to split string by line break in Excel
AAA
BBB
CCC
DDD
EEE
FFF
GGG
How to split string in DDD, EEE, FFF, GGG
This is very helpful site for excel problems .... i used it .. and its good
I Wanna break this strings RNY0HC3B282001573F14 in below mentioned pattern in different column, how i can using function please reply
RNY0 HC 3B28 2 157 3 F 14
Hello Svetlana,
Thank you for your examples! I am trying to do this exact same thing, only in DAX for Power Pivot. My situation would be like your "Dress-Blie" example above, and I am trying to return "Dress"..
John
I am trying to split a column of e-mail addresses into 2 cols (E-MAIL1 and E-MAIL2), when the original column may have only a single address or may have 2 addresses separated by a comma, like: jdoe@dom.com, mdoe@dom.com"
My problem is that SEARCH() and FIND() both seem to return #VALUE errors when there is only one e-mail address in the source column (and thus the "," can't be found.)
Can I work around this by, for example, testing for an error (like if(SEARCH(",",sourcecol)=#VALUE, . . . etc.?)
Or some other way?
Thanks,
Martin
Hi
I've been looking all morning to find a way to split out the following;-
Amazon EU AMAZON.CO.UK LUX 99.99
SPAR LOCHINVER LAIRG 99.99
Can't split by fixed length, or delimiter, tried a macro to strip out text and then numbers but all costs end up as 9999 and not as a monetary amount.
I have 12 months worth of old statements, about 1500 transactions, to reformat correctly. They are only available in as a pdf file not a csv which is what makes this a challenge.
Any help, suggestions much appreciated.
Thanks
PFS
michal jackson , BScIT, MCA, LLB, FAICD
This is the given string . I want to split the string into two parts. That is I want to split BScIT, MCA, LLB, FAICD into degree part and michal jackson into name part.
How should i do it.
hi,
I need to split the text in the string in different columns. Columns are fixed.
For Example:
Input String: Application_PROPAY , Area_Feeds, Assigned_to_PTS, Cause_Data Issue-Incorrect Data, Detected_RBS
Output should be like: PROPAY should go to column name Application
Feeds should go to column name Area
PTS should go to Assigned to etc..
In some cases all the column names might not have mentioned.. please help
I have a character string, for example: GTHSYSKSSGSJSJGSUSKKS
I want each character in its own column. How would I do that?
Thanks
Hello Svetlana,
I need your help on this.
Is there a way that I can split a string of text in a cell from
"This is 2nd enquiry from this client. The 1st time wasSep-329 in Union TalkHusband's name: ChanWaiCheongTel:9*****"
into
"This is 2nd enquiry from this client. The 1st time wasSep-329 in Union Talk Husband's name: Chan Wai Cheong Tel:9*****"
I have 44500 records having this problem. I tried doing a VBA code, to add space whenever there is a CAPs letter but it wont work for some records as the outcome will be: "paid via VISA" into "paid via V I S A"
Please do help. Thank you.
I have managed to solve this with a VBA Code.
Function SplitCaps(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Global = True
.Pattern = "([a-z])([A-Z])"
SplitCaps = .Replace(strIn, "$1 $2")
End With
End Function
If any of you have another alternative please do share.
Thanks
Hello Svetlana,
I like your information above, but as usual I have a different split column issue.
So... I'm looking for a way to split a cell of text into to. The split must not come more that 30 characters (Including spaces) from the left but must be at a space or the immediate previous space to the 30th character point.
So if the cell contained: "EMERGENCY MU/INITIAL FILL TO CONDENSER CTRL VAL"
The 30th Character is the "C" of Condenser so the cell will split between "TO" and "Condenser"
How do I write this code please?
Many thanks for your help,
Tim
1) face redness / Erythema (n);
2) felt hot / Feeling hot (n);
3) felt faint / Dizziness (n);
4) dizziness / Dizziness (n);
5) hands and feet weakness / Muscular weakness (n);
6) arterial pressure increased to 140/70mmHg / Blood pressure systolic increased (n);
7) flushes / Flushing (n);"
The above data is a singe cell data i need to seperate from cell to column after every semicolon.
While trying it on text to coloumn option of excel I cannot see any data getting shifted to the adjacent columns
Hi, Ihave Query regarding Excel function.
Iwant to split the Number after symbol For Ex:
Putcharoen - Patumwan - T003
Butthongkomvong - Muang - 5104
Yim - Cheongju-si - 9573
Hwang - Seongnam-Si - 3018
so, from this i want values from the last symbol i.e. T003,5104,3018 like this ....
Please provide me the Formula for this.
Thanks in Advance
Thanks,
Adithya
Can you help with Split string by dash:
Item-Color-Size-FIT pattern, and you want to split the column into 4 separate columns?
Item and color are the same as above. Having trouble with Size and FIT.
Hi
I want to split the below into columns
90FB5B8F6B40
001BBA9AB00
001BBA99FE00
I want my end result to be
90:FB:5B:8F:6B:40
00:1B:BA:A9:AB:00
00:1B:BA:99:FE:00
I thought if i can split the text to single columns and concatenate to join them back inserting the ":" that would help. So i needed to know how to separate the text string into columns. Is that possible?
Based on your data being in A1, and always being the same length and expected return
=LEFT(A1,2)&":"&MID(A1,3,2)&":"&MID(A1,5,2)&":"&MID(A1,7,2)&":"&MID(A1,9,2)&":"&MID(A1,11,2)
if want them to be in separate column start with LEFT(A1,2)
and then for every subsequent col copy the MID(A1,3,2) parts
Hello Svetlana Cheusheva,
Can you help me to extract "Fort Walton Beach", "Jacksonville Beach","Wethersfield",etc.
These are examples:
545 Ashley Court, Fort Walton Beach, FL 32547
713 Arch Street, Jacksonville Beach, FL 32250
341 Cardinal Drive, Wethersfield, CT 06109
134 Main Street East, Anchorage, AK 99504
211 Oak Lane, West Lafayette, IN 47906
458 Williams Street, El Dorado, AR 71730
913 Liberty Street, Saint Paul, MN 55104
860 Somerset Drive, Acworth, GA 30101
36 Briarwood Drive, Dekalb, IL 60115
Thank you so much.
Tien
=MID(A10,FIND(",",A10)+2,(FIND(",",A10,FIND(",",A10)+1)-(FIND(",",A10)+2)))
looks for the commas and uses mid to select the text between them.
Assumes text is in A10.
I have a long column of number letter combinations I want to split at the first letter (any letter)
eg. 43245tre becomes 43245 tre
129ftr becomes 129 ftr
Help?
Hello Mark,
Do you want to split numbers and letters in 2 separate columns, or separate them with a space within a cell?
what if we want to split my string e.g. or and. How do you specify split only when or or and is byitself and not part of a word. The below keeps happening.
dog or cat slit into rows dog
cat
orange and apple or
ange
apple
Hi Lila,
You can use the 'Split by string' option of our add-in. When entering the delimiter strings, just be sure to type a space before and after or to prevent splitting words.
And thank you for this great question! I will certainly include this tip in the article.
04 particulars in 04 row how we make the one coloumn. in excel
1
Name Address mail add phone no.
Smt Anju Devi Dhanka, IND
Bassi ( Jaipur)
mlabassi2009[at]yahoo[dot]com
M-8, Gandhi Nagar, Jaipur.,
0141-6594942
Hello Subhash,
Sorry, your task is not quite clear. If you can send us a sample worksheet with the original data and expected result, our support team will try to help.
Telephone error: Please enter the telephone number (area code and telephone number separated by a space or delimiter) AM STUCK