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
Is it just the way the syntax was created?
SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1)
Hi Nathan,
To explain it better, let's have a look at the full formula:
=MID(A2, SEARCH("-", A2) + 1, SEARCH("-", A2, SEARCH("-",A2) +1) - SEARCH("-", A2) - 1)
1. Start_num argument: SEARCH("-",A2) + 1. It finds the position of the first character to extract; +1 is needed to start extraction beginning with the character that follows the first hyphen, not including the hyphen itself.
2. Num_chars argument (how many characters to extract): SEARCH("-", A2, SEARCH("-",A2) +1) - SEARCH("-", A2) - 1. To find the position of the 2nd hyphen, you put SEARCH("-",A2)+1) in the start_num of the first SEARCH function to tell it to start searching beginning with the character after the 1st hyphen (this way, you exclude the first hyphen from search). Then, you subtract the position of the 1st hyphen from the position of the 2nd hyphen to find how many characters are between them. But this number includes the 2nd hyphen, so you put -1 at the end to remove the 2nd hyphen from the returned substring.
In example #2 above, can you explain the +1 when finding the difference between the 2nd and 1st hyphens and how does the last search argument explains how the function understands how to extract all the characters between the two hyphens?
I have a full description of a part in one column and need to pull over no more then 35 characters. I need to do this at a "space" but not go over 35 characters on my limit. please help!
thanks!!
=LEFT(A1,FIND("☃",SUBSTITUTE(A1," ","☃",LEN(LEFT(A1,36))-LEN(SUBSTITUTE(LEFT(A1,36)," ","")))))
(Left(a1,36)) This trims the string to a maximum of 36 characters. 36 because if the 36th is a space then 35 chars will be returned and if not, then the break will come earlier in the string and less will be returned.
The statement LEN-LEN(substitute) finds the length of the string, and then the length with the spaces removed. This gives the number of spaces in the string.
The Substitute inside the Find statement then changes the last space to a snowman, and the Find returns that position in the string.
Finally the left statement cuts the string at that point and gives your value.
Hi , thanks a lot for this, but i have a big problem , it's only for 3 column, i am working as data entry and i have 10 column , hot to do this please help
Gemcitabine_pow_1000 mg_1 X 1V
Ranitidine_tab MR_150 mg_6 X 10T
Ranitidine_tab MR_300 mg_3 X 10T
Above text please separate from "_" (Only by formula) , Not by text to column
Please send me formula
I have the following data in one cell. i like to spit these data in different cells. is anybody can help?
1/15/2019 4320 610285062 1 30 0930 1143 88 167.75 14.26 $182.01
in Excel select the cell/column.
On the ribbon, choose Data tab, "Text to Columns" and select delimiter and then untick Tab and select Space
i have data in excel
A battery consists of (a) only one cell (b) only two cells (c) two or more cells (d) All of the above
Column A Column B Column C Column D Column E
i need to separate Data for example
1. Before (a) Column A
2. After (a) and Before (b) Column B
3. After (b) and Before (c) Column C
4. After (C) and Before (D) Column D
5. After (D) Column E
pls help
I have a text string that I am trying to split which has different characters to split in the string.
Example string
Product Support 2018 H2~TC03986-01-ICS
I want to split it as "Product Support 2018 H2" "TC03986-01" and "ICS"
I am not sure if it is possible to use two different separators, but the "~" causes a problem. I have tried to use CHAR(126) but this does not work. I have also tried to replace the ~ using search and replace.
Part answer:
=LEFT(C20,FIND("~",C20)-1) to get product
Arvind:
To split this string I would use the Text-to-Column tool.
You can find it under the data tab. Use the fixed width option and after you enter the comma as a field choose the Do Not Import option for the comma.
I want to split this
Sr. N0 first name last name postal code
1 jokovic,novok (SRB) 13630
HOW ?
String is $GPRMC,062745.044,A,1830.2470,N,07350.5297,E,1.07,44.40,101018,,,A*54
how to split into columns with removal of comma with formula.
ex-
1st column=$GPRMC
2ND COLUMN=062745.044
3RD COLUMN=A
4TH COLUMN=1830.2470
5TH COLUMN=N
6TH COLUMN=07350.5297
WHAT FORMULA SHOULD I USE. I need only formula or macro code not other ready made option in excel.
Thanks
I want to extract the City from the following rows. The City is the word in between the LAST space of the field and the comma of the field. The comma is always the last character in the field. What formula should I use?
1111 FM 1585 LUBBOCK,
2222 10TH ST SHALLOWATER,
333 COLTON AVE LUBBOCK,
Hello, Claudia,
Suppose your data are in column A, please try to enter the following formula in cell B1 and then copy it down along the column:
=MID(A1, FIND("#", SUBSTITUTE(A1, " ", "#", LEN(A1)-LEN(SUBSTITUTE(A1, " ", ""))))+1, LEN(A1)-FIND("#", SUBSTITUTE(A1, " ", "#", LEN(A1)-LEN(SUBSTITUTE(A1, " ", ""))))-1)
Hope this is what you need.
Hi,
how can you split string "abcdef" in a single character per row?
Swati:
The simplest method to accomplish what you want is to use the Text-to-Columns tool. People seem to turn their collective nose up at using this tool and I don't know why.
Highlight the cell containing the data to split.
Select under the Data tab on the ribbon select the Text-to-Columns option.
In the Text-to-Columns window select the Fixed Width radio button.
Left click the places where you want the data to be split.
Click finish.
This will leave the first character in the original cell and the data will be in separate cells.
If you have multiple rows containing data to be split, highlight all the rows and go through the same process.
You can split multiple rows, but not multiple columns.
Doug, the reason that this doesn't work for me is that I have an export where the data looks like this: 422353-1-5 but the first number can be any length up to 7 digits. If the first number is from about 1900 to 9999, the Excel sheet has those cells formatted as a date with the correct ones formatted like a string. I need to determine on a row by row basis whether it's a date so I use an IFERROR function to split it into 3 columns.
If I only had to do this once, I would simply do this: =IFERROR( YEAR(B2)&"-"&MONTH(B2)&"-"&DAY(B2), B2) and then split it into 3 columns, but this is something that needs to be done all of the time and it's faster to do it by having a proper formula that can be copied down.
Formulas for splitting text string with spaces into columns worked perfectly.
Thank you.
Hi
Irregular word and Number in excel 1 column to multi column like
Example:JSE SALE 2800 DRS CASH 2815
1. words in 1 column
2. Numbers in 1 column
Clarence:
I can't tell if there is a space between the first decimal and the "1". If there isn't, I think this will work for you. Where the original string is in K1 enter this in an empty cell.
=MID(K1,1,3)&" "&MID(K1,5,2)&""&MID(K1,7,4)&""&MID(K1,11,3)&" "&MID(K1,15,3)&""&MID(K1,18,7)&" "&MID(K1,25,7)
I hope you see how this formula works. It counts the characters including spaces goes to the next number of characters indicated by the second number and then returns that number of characters followed by a space or in some cases no space if there already was a space in the original. I got tired of counting characters.
If you need a space after the first decimal add another &""&MID(K1,etc)
like this:
=MID(K1,1,3)&" "&MID(K1,5,2)&""&MID(K1,7,4)&""&MID(K1,11,3)&" "&MID(K1,15,3)&""&MID(K1,18,4)&" "&MID(K1,22,3)&" "&MID(K1,25,7)
I need a help with a formula.. We get some data interfaced to our system but due to some error at source it is stuck in the temporary area and does not move to the live area. The error is for eg.
DXB EK LON AC YYZ 234.123567.34 the error is that 2 amounts are merged. The result I require is that after 2 decimal places I need a space like DXB EK LON AC YYZ 234.12 3567.34. Would I be able to get the correct string in another cell using excel formula.
=LEFT(A1,FIND(".",A1)+2)&" "&RIGHT(A1,(LEN(A1)-FIND(".",A1))-2)
I've been trying the solutions here to no avail.
I am trying to get an input into a cell visually altered inside the same cell
From, when numbers are entered:
01234567890
To, when the cell is left:
012 3456 7890
Anyone got any ideas
Hello
I have following value
Cell A1 = 621.33Ari
A2 = 620.4.7Dsi(5)
A3 = 53:62Asi:21
I want separate text from number
For example
Output to cell B1 = 621.33 Cell C1 = Ari
B2 = 620.4 Cell C2 = Dsi(5)
B3 = 53:62 Cell C3 = Asi:21
Please help me
Thanks
Bhagwan:
I think you'll need two little formulas to get this done.
For the first set of data enter this in B1
=LEFT(A1,6) and this in C1 =RIGHT(A1,3)
For the next two sets use this in B2 =LEFT(A2,6) and enter this in C2 =RIGHT(B2,6)
Copy these two formulae down the column for B3 and C3.