*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, *Flash Fill* and *Split Names* add-in. Today, we are going to take an in-depth look at how you can split strings using formulas and the *Split Text* feature.

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.

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

As you probably know, the Excel MID function has the following syntax:

MID(text, start_num, num_chars)Where:

- text - where to extract the text from.
- start_num - the position of the first character to extract.
- num_chars - the number of characters to extract.

In the above formula, the text is extracted from cell A2, and the other 2 arguments are calculated by using 4 different SEARCH functions:

**Start number**(start_num) is the position of the first hyphen +1:`SEARCH("-",A2) + 1`

**Number of characters to extract**(num_chars): the difference between the position of the 2^{nd}hyphen and the 1^{st}hyphen, minus 1:`SEARCH("-", A2, SEARCH("-",A2)+1) - SEARCH("-",A2) -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 2

^{nd}hyphen. The difference is the number of characters after the 2^{nd}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** (" "), **slash** ("/"), **colon** (";"), **semicolon** (";"), and so on.

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:

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.

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.

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:

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"))`

The detailed explanation of the formula's logic can be found here.

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(B2, LEN(A1)-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)`

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:

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.

An alternative way to split a column in Excel is using the **Split Text** feature included with our Cell Cleaner add-in, which provides the following options:

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

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 the Cell Cleaner add-in installed (the trial version can be downloaded here), 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.

- Expand the

Done! The task that required 3 formulas and 5 different functions now only takes a couple of seconds and a button click.

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:

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:

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:

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 1
^{st}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, here's the trial version to download. And if you are happy with the Split Text performance, you can buy this add-in individually or as part of our Ultimate Suite for Excel. Either way, be sure to grab the 15% off promotion code that we offer exclusively to our blog readers: AB14-BlogSpo

I thank you for reading and hope to see you on our blog next week!

Excel Split Cells formulas (.xlsx file)

Excel formulas
CSV
Excel functions
Print
Vlookup in Excel
Merge data in Excel
SumIf
Excel CountIf
Excel Compare
Excel If statement
Excel Charts
Pivot Table
Updates
Excel conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 156 Responses to "How to split text string in Excel by comma, space, character or mask"

I have output from a Qualtrics survey for "select all that apply" questions. I need to import it into a statistical package for analysis (SPSS). The data now have numeric codes for answer choices, but when imported into the stats package are read as string (alpha) and separated by commas. I am trying to use Exsel to separate these values into columns, but need a different column for each value. Some questions have up to 5 values, but users may have only checked 1, 2 or even 4 or 5 options, and so their values vary.

Original Data: --> New array, where | | indicates a separate column

1, 2, 5 becomes | 1 | 2 | | | 5| (i.e. the columns for the values 3 and 4 are left blank)

2, 3, becomes | | 2 | 3 | | | (i.e. the columns for the values 1, 4 and 5 are left blank)

4 becomes | | | | 4 | | (i.e. the columns for the values, 1, 2, 3 and 5 are left blank)

Text to column separates them, but then I have a column with a mixture of answers (e.g. 1, 2, 4 based on example above) yet I need to create a dichotomous variable for answer choice 1, a separate one for 2, etc. Any ideas? Thank you.

I don't know if you can do it other than with a formula:

______A___ | ____________B______________ ... ____________F______________

1 | "1,2,5" | =if(iserr(find("1",A1)),"",1) ... =if(iserr(find("5",A1)),"",5)

2 | ...

3 | "4" ____| =if(iserr(find("1",A3)),"",1) ... =if(iserr(find("5",A3)),"",5)

Hello Sara!

If I understand your task correctly, please try the following formula:

For example, the value of cell A1 is 1,2,5

Formula in cell B1

=IFERROR(IF(--FIND("1",A1,1) > 0,1,""),"")

Formula in cell C1

=IFERROR(IF(--FIND("2",A1,1) > 0,2,""),"")

Formula in cell D1

=IFERROR(IF(--FIND("3",A1,1) > 0,3,""),"")

Formula in cell E1

=IFERROR(IF(--FIND("4",A1,1) > 0,4,""),"")

Formula in cell F1

=IFERROR(IF(--FIND("5",A1,1) > 0,5,""),"")

I hope this will help, otherwise please do not hesitate to contact me anytime.

Thank you!

Hello,

I'am looking for this function:

I have a cell like this : XTORM SOLID BLUE MICRO USB CABLE 1MTR

I want to split this cell (all cells with text) after the 3rd or 4rd space, like this

XTORM SOLID BLUE

MICRO USB CABLE

1MTR

Those words undernead in 1 cell

Is this possible?? i hope thanks!!! greetz remco

Hello Remco!

To divide the text into 3 cells after every third space, use the formulas

=LEFT(C1,FIND("*",SUBSTITUTE(C1," ","*",3))-1)

=MID(C1,FIND("*",SUBSTITUTE(C1," ","*",3))+1, FIND("*",SUBSTITUTE(C1," ","*",6)) -FIND("*",SUBSTITUTE(C1," ","*",3)))

=IFERROR(MID(C1,FIND("*",SUBSTITUTE(C1," ","*",6))+1, FIND("*",SUBSTITUTE(C1," ","*",9))-FIND("*",SUBSTITUTE(C1," ","*",6))), MID(C1,FIND("*",SUBSTITUTE(C1," ","*",6))+1, LEN(C1)-FIND("*",SUBSTITUTE(C1," ","*",6))))

I hope this will help

Hi..

Plz help me to count the frequency in the following

1234567 = frequency is 7 than plz help me to find correct formula for count frequency in the following examples

1.3..67 = 4 and

7 = 1 and

...3...7 = 2

Plz help

Hello!

Tell me specifically what exactly you want to calculate. "1234567 = frequency is 7" is not a frequency. Perhaps you want to find the number of digits? Or the number of characters? Or something else?