*The tutorial shows you how to split cells in Excel using formulas and Split Text function. You will learn how to separate text with a comma, space, or other separator and how to split strings into text and numbers*.

Splitting text from one cell into multiple cells is the task that all Excel users face every now and then. We discussed in one of our previous articleshow to split cells in excelthe habits*Text to column*characteristics*Flash fill*. Today we'll take a closer look at how to split strings using formulas and the*Split text*tool.

## How to Split Text in Excel Using Formulas

To split string in Excel, you usually use LEFT, RIGHT or CENTER function in combination with FIND or SEARCH. At first glance, some of the formulas may seem complicated, but the logic is actually quite simple, and the following examples will give you some clues.

### Split string by comma, semicolon, slash, dash, or other separator

When splitting cells in Excel, it is important to locate the position of the delimiter within the text string. Depending on your task, this can be done by using case sensitiveSEARCHor case sensitiveFIND. Once you have the position of the delimiter, use the RIGHT, LEFT, or CENTER function to extract the corresponding part of the string. For a better understanding, let's look at the following example.

Suppose you have a list of SKUs from the*Item-color-size*pattern and you want to split the column into 3 separate columns:

- Around the
**Item name**(all characters before the first hyphen), insert the following formula into B2 and copy it down the column:`=LEFT(A2,LOOKUP("-",A2,1)-1)`

In this formula, LOOKUP determines the position of the first hyphen ("-") in the string, and theLEFT functionextracts all remaining characters (you subtract 1 from the position of the hyphen because you don't want to extract the hyphen itself).

- Around the
**colour**(all characters between the 1st and 2nd hyphen), enter the following formula in C2, and then copy it to other cells:`=MIDDLE(A2, LOOKUP("-",A2) + 1, LOOKUP("-",A2,LOOKUP("-",A2)+1) - LOOKUP("-",A2) - 1)`

In this formula we use theExcel MID functionto extract text from A2.

The starting position and the number of characters to be extracted are calculated using 4 different SEARCH functions:

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

**Number of characters to extract**: the difference between the position of the 2^{zd}hyphen and the 1^{st}hyphen, minus 1:`LOOKUP("-", A2, LOOKUP("-",A2)+1) - LOOKUP("-",A2) -1`

- Around the
**maat**(all characters after the 3rd hyphen), enter the following formula in D2:`=RIGHT(A2,LEN(A2) - LOOKUP("-", A2, LOOKUP("-", A2) + 1))`

In this formula, theLEN functionreturns the total length of the string, from which you subtract the position from the 2

^{zd}hyphen. The difference is the number of characters after the 2^{zd}hyphen, and the RIGHT function extracts them.

In a similar way, you can split the column by any other character. For example, all you need to do is replace "-" with the required separator**room**(" "),**come**(","),**slash**("/"),**colon**(";"),**semicolon**(";"), and so forth.

Tip.In the formulas above, +1 and -1 correspond to the number of characters in the delimiter. In this example it is a hyphen (1 character). If your separator is 2 characters, e.g. a comma and a space, then just add the comma (",") to the SEARCH function and use +2 and -2 instead of +1 and -1.

### Split string by line break in Excel

Use formulas similar to those in the previous example to split text by space. The only difference is that you need the CHAR function to specify the line break character, as you cannot type it directly into the formula.

Suppose the cells you want to split look something like this:

Take the formulas from the previous example and replace a hyphen ("-") with CHAR(10), where 10 is the ASCII code for line feed.

- Around the
**Item name**:`=LEFT(A2,LOOKUP(CHAR(10),A2,1)-1)`

- Around the
**colour**:`=CENTRE(A2, LOOKUP(CHAR(10),A2) + 1, LOOKUP(CHAR(10),A2,LOOKUP(CHAR(10),A2)+1) - LOOKUP(CHAR(10),A2) - 1 )`

- Around the
**maat**:`=RIGHT(A2,LEN(A2) - LOOKUP(CHAR(10), A2, LOOKUP(CHAR(10), A2) + 1))`

And this is what the result looks like:

### 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 specific string pattern. Below are the formulas for the two common scenarios.

### Split string of 'text + number' pattern

Suppose you have a column of strings with text and numbers combined, where a number always follows text. You want to split the original strings so that the text and numbers appear in separate cells, like this:

The result can be achieved in two different ways.

#### Method 1: Count the digits and extract as many characters

The easiest way to split a string where the number comes after the text is as follows:

Nasty**extract numbers**, search the string for every possible number from 0 to 9, get the sum of the numbers, and return that many characters from the end of the string.

With the original string in A2, the formula goes like this:

`=RIGHT(A2,SUM(LEN(A2) - LEN(REPLACE(A2, {"0","1","2","3","4","5","6","7" ,"8","9"},""))))`

Nasty**extract text**, calculate the number of text characters in the string by subtracting the number of extracted digits (C2) from the total length of the original string in A2. Then use the LEFT function to return that many characters from the beginning of the string.

`=LINKS(A2,LENGTE(A2)-LENGTE(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 1^{st}digit in a string

An alternative solution would be the following formula to determine the position of the first digit in the string:

`=MIN(LOOKUP({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.

Extract**text**:

`=LINKS(A2; B2-1)`

Extract**number**:

`=RECHTS(A2, LENGTE(A2)-B2+1)`

Where A2 is the original string and B2 is the position of the first number.

To remove the helper column holding the position of the first digit, you can embed the MIN formula in 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(LOOKUP({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)`

### Split string of 'number + text' pattern

If you split cells where text appears after number, you can**extract numbers**with the following formula:

`=LEFT(A2, SUM(LEN(A2) - LEN(REPLACE(A2, {"0","1","2","3","4","5","6","7" ,"8","9"},""))))`

The formula is similar to the formula discussed in the previous example, except that you use the LEFT function instead of the RIGHT function to get the number to the left of the string.

Once you have the numbers,**extract** **text**by subtracting the number of digits from the total length of the original string:

`=RECHTS(A2,LENGTE(A2)-LENGTE(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 string use either onethis formulaof deUnpack tools. Or you can create a custom function for itsplit numbers and textin separate columns.

Thus, you can split strings in Excel with different combinations of different functions. As you can see, the formulas are far from obvious, so you may want to download the sampleExcel Split Cells Workbookto examine them more closely.

If figuring out the arcane twists of Excel formulas isn't your favorite thing to do, you might like the visual method of splitting 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 to use theSplit textfeature included with our Ultimate Suite for Excel, which offers the following options:

- Split cell by character
- Splits cel per string
- Split cell by mask (pattern)

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

### Split cells by character

Choose this option when you want to split the cell contents**every time the specified character occurs**.

For this example, let's take the strings of the*Item-color-size*pattern we used in the first part of this tutorial. As you may remember, we separated them into 3 different columns using3 different formulas. And this is how you achieve the same result in 2 quick steps:

- Assuming you haveUltimate sequelinstalled, select the cells you want to split and click on it
**Split text**icon on the*Ablebits data*tab. - Of
*Split text*The pane opens on the right side of your Excel window and you do the following:- Fold the
**Split by character**group and select one of the predefined separators or type another character in the*Amended*box. - Choose whether to split cells into columns or rows.
- View the result below
*Example*section and click the**Divide**knob.

- Fold the

Tip.If there can be more than one consecutive delimiter in a cell (for example, more than one space), select the*Treat consecutive separators as one*box.

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

### Split cells by string

This option allows you to split strings using**any combination of characters**as separator. Technically, you split a string into parts by using one or more different substrings as the boundaries of each part.

For example, to split a sentence by the conjunctions "*In*" In "*of*", fold the**Split by strings**group and enter the delimiter strings, one per line:

As a result, the source sentence is separated on each occurrence of each delimiter:

Tip.The characters "or" and "and" can often be part of words like "orange" or "Andalusia", so make sure you have a**room**before and after*In*In*of*to avoid split words.

And here's another example from practice. Let's say you imported a column of dates from an external source that looks like this:

*5.1.2016 12:205.2.2016 14:50*

This format is not conventional for Excel, and therefore none of theDate functionswould recognize any of the date or time elements. To split the day, month, year, hours, and minutes into individual cells, enter the following characters*Split by strings*box:

- Dot (.) to separate day, month, and year
- Colon (:) to separate hours and minutes
- Space to separate date and time

Press on the*Divide*button, and you immediately get the result:

### Split cells by mask (pattern)

To separate a cell by a mask means to split a string**based on a pattern**.

This option is very useful when you need to split a list of homogeneous strings into single elements or substrings. The complication is that the source text cannot be split every time a certain delimiter occurs, but only at one or more specific times. The following example will make things easier to understand.

Suppose you have a list of strings extracted from a log file:

What you want is date and time, if any, error code and exception details in 3 separate columns. You cannot use a space as a separator because there are spaces between date and time, which should appear in one column, and there are spaces in the exception text, which should also appear in one column.

The solution is to split 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 it**Split by mask**section about the*Split text*type the mask in it*Enter delimiters*box and click*Divide*:

The result looks something like this:

Remark.Split string by mask is**case sensitive**. So make sure you type the characters in the mask exactly as they appear in the source strings.

A major advantage of this method is its flexibility. For example, if all 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 plugin to divide the original strings into 4 parts:

- Found all characters before the 1st space within the string (date)
- Signs between 1
^{st}space and the word*WRONG:*(time) - Text in between
*WRONG:*In*Exception:*(error code) - Everything that comes after
*Exception:*(exception text)

Hope you liked this quick and straight forward way to split strings in Excel. If you're curious to give it a try, you can download an evaluation version below. Thank you for reading and hope to see you on our blog next week!

## Available downloads

Excel Split Cells-formules(.xlsx file)

Ultimate Suite 14-day fully functional version(.exe file)

## You may also be interested in

- How to split cells in Excel
- TEXTSPLIT Function: Quick way to split cells with a separator
- How to undo cells in Excel
- Separate names in Excel: Split first and last name into different columns
- Split date and time in Excel
- How to Merge Two Columns in Excel Without Losing Data