*This tutorial explains how to use formulas and the Split Text feature to split cells in Excel. You will learn how to separate text with commas, spaces or any other delimiter and how to split strings into text and numbers*.

Splitting text from one cell to multiple cells is a task that all Excel users face from time to time. In one of our previous articles we discussedHow to split cells in Exceluse*text in a column*operation i*fast charging*.Today we'll take a closer look at how to use the formulas and*text division*tool.

## How to split text in Excel using a formula

To split a range in Excel, you typically use the LEFT, RIGHT, or CENTER functions in conjunction with FIND or SEARCH. At first glance, some formulas may seem complicated, but the logic is actually very simple, the following examples will give you some hints.

### Separate strings with commas, semicolons, slashes, dashes, or other delimiters

When separating cells in Excel, locating the separator within the text string is key. Depending on your task, this can be done using case-insensitivelooking foror case sensitiveI am looking. After determining the position of the delimiter, use the RIGHT, LEFT, or MID function to extract the appropriate portion of the text string. For better understanding, let us consider the following example.

Let's say you have a list of SKUs*Product-Color-Size*shape and you want to split the column into 3 separate columns:

- extract
**Name of the product**(all characters before the first dash), enter the following formula in B2 and copy it into column:`=Left(A2, Search("-",A2,1)-1)`

In this formula, SEARCH determines the position of the first hyphen ("-") in the string ileft modeOutputs all remaining characters (subtract 1 from the hyphen position because you don't want to output the hyphen itself).

- extract
**colour**(everything between the first and second dashes), enter the following formula in C2 and copy it to the other cells:`=MIDD(A2,SEARCH("-",A2) + 1,SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1)`

In this formula we useExcel MID functionExtract the text from A2.

The starting position and the number of characters to extract are calculated with the help of 4 different SEARCH functions:

**start number**is the position of the first dash +1:`search("-",A2) + 1`

**the number of characters to export**: Difference between position 2^{nd}hyphen and 1^{Yingshi}Crtica, minus 1:`search("-", A2, search("-",A2)+1) - search("-",A2) -1`

- extract
**Size**(all characters after the 3rd dash), enter the following formula in D2:`=RIGHT(A2,LEN(A2) - Search("-", A2, Search("-", A2) + 1))`

In this type,function of lengthReturns the total length of the string minus 2

^{nd}dash. The difference is in the number of characters after 2^{nd}dashes, the RIGHT function outputs them.

Similarly, you can separate columns with any other character. All you need to do is replace the "-" with the desired separator, e.g.**space**(""),**notch**(","),**restrict**("/"),**colon**(";"),**semicolon**(""") etc.

advice.In the formula above, +1 and -1 correspond to the number of characters in the separator. In this example it is a dash (1 character). If your delimiter consists of 2 characters, such as a comma and a space, enter only a comma ("") in the SEARCH function and use +2 and -2 instead of +1 and -1.

### How to split a table using a new line in Excel

To separate the text with spaces, use a formula similar to the one shown in the previous example. The only difference is that you need the CHAR function to insert a new row because you can't type it directly into the formula.

Let's say the cell you want to split looks like this:

Using the formula from the previous example, replace the hyphen ("-") with CHAR(10), where 10 is the ASCII code for the newline character.

- extract
**Name of the product**:`=Left(A2,Search(Character(10),A2,1)-1)`

- extract
**colour**:`=MIDD(A2, SEARCH(CHARACTER(10),A2) + 1, SEARCH(CHARACTER(10),A2,SEARCH(CHARACTER(10),A2)+1) - SEARCH(CHARACTER(10),A2) - 1 )`

- extract
**Size**:`=RIGHT(A2,LEN(A2) - SEARCH(CHARACTER(10), A2, SEARCH(CHARACTER(10), A2) + 1))`

This is what the result looks like:

### How to split text and numbers in Excel

First, there is no general solution that works for all alphanumeric strings. Which type to use depends on the particular string pattern. Below you will find formulas for two common cases.

### Split the string in text+number mode.

Suppose you have a column of strings containing text and numbers, where the numbers always follow the text. You want to break the original string 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 numbers and output that many characters

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

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

For the original table in A2, the formula is as follows:

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

reach**extract text**, you can calculate the number of 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, use the LEFT function to return that many characters from the beginning of the string.

`=左(A2,LEN(A2)-LEN(C2))`

Among them, A2 is the original string and C2 is the extracted number, as shown in the screenshot:

#### Method 2: Find position 1^{Yingshi}numbers in sequence

Another solution is to use the following formula to determine the position of the first number in the sequence:

`=MIN(search({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))`

Once you find the position of the first number, you can use a very simple LEFT and RIGHT formula to split the text and numbers.

extract**text**:

`= Left (A2, B2-1)`

extract**number**:

`=DESNO(A2, LEN(A2)-B2+1)`

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

To get rid of the auxiliary column that stores the position of the first number, you can embed the MIN formula inside the LEFT and RIGHT functions:

formula extract**text**:

`=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)`

formula extract**number**:

`=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)`

### Split a series of "Number+Text" patterns.

If you want to split cells where text appears after a number, you can**extract the numbers**Use the following formula:

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

This formula is similar to the one described in the previous example, except that you use the LEFT function instead of the RIGHT function to get the number on the left side of the string.

When you get the numbers,**refining** **text**Subtracting the number of bits from the total length of the original string:

`=右(A2,LEN(A2)-LEN(B2))`

Among them, A2 is the original string and B2 is the extracted number, as shown in the figure below:

advice.I get**digits in any position**In the text string, usethis formulatheextraction tool.or you can create a custom function forShare numbers and textin a separate column.

This is how you can use different combinations of different functions to split strings in Excel. As you can see, the formula is far from obvious, so you might want to download the exampleExcel workbook with separated cellsCheck them again.

If you are not interested in understanding the arcane twists and turns of Excel formulas, you may enjoy the visual way to split cells in Excel, which is shown in the next part of this tutorial.

## How to use the Split Text tool to split cells in Excel

Another way to split columns in Excel is to usetext divisionThe Ultimate Suite for Excel includes features that provide the following features:

- Separate cells by character
- Split cells by table
- Split cells by mask (pattern)

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

### Separate cells by character

Select this option whenever you want to split the contents of a cell**each occurrence of the specified character**.

For this example, let's put in a table*Product-Color-Size*The pattern we used in the first part of this tutorial. As you may remember, we split them into 3 different columns using3 different recipes.See how you can achieve the same result in 2 quick steps:

- suppose you haveGreat apartmentAfter installation, select the cell you want to split and click
**text division**icon enabled*Ability data*Let me underline. - this
*text division*A window will open on the right side of the Excel window where you can:- expand
**divided by character**group and select one of the predefined separators or*customized*Box. - Choose whether to divide the cells into columns or rows.
- See the results below
*preview*section and then click on**Division**button.

- expand

advice.If there are many consecutive separators in the cell (for example, many spaces), select*Treat consecutive delimiters as one*Box.

complete! A task that required 3 types and 5 different functions now takes only a few seconds and a click of a button.

### Split cells by table

This option allows you to split a string using**any combination of characters**as a separator. Technically, you can split an array into chunks using one or more different substrings as boundaries for each chunk.

For example, using the link "*I*"I"*the*", they expand**divided by a string**group and insert separator strings, one per line:

As a result, the original expression is split on each occurrence of each delimiter:

advice.The characters "or" and "and" can often be part of words like "orange" or "Andalusia", so be sure to include**space**before and after*I*I*the*to avoid splitting words.

Here is another real example. Suppose you import a date column from an external source like this:

*1.5.2016 12:205.2.2016 14:50*

This format is not normal for Excel, so it does not existdate functionAll date or time elements are recognized. To split the day, month, year, hour, and minutes into separate cells, use*divided by a string*Box:

- A dot (.) separates the day, month and year
- A colon (:) separates hours and minutes
- a space separating the date and time

to win*Division*and you will immediately get the result:

### Split cells by mask (pattern)

Splitting cells with a mask means splitting the array**based on the sample**.

This option is useful when you need to split a list of homogeneous strings into specific elements or substrings. The complexity is that the source text cannot be separated in every occurrence of a given delimiter, only in some specific occurrences. The following example will make it easier for you to understand.

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

What you want is to have the date and time (if any), error code and exception details in 3 separate columns. You cannot use spaces as separators because there are spaces between datetimes, they should appear in one column, and there are spaces in the exception text, they should also appear in one column.

The solution is to split the array with the following mask:** Error: * Exception: **

where an asterisk (*) represents any number of characters.

Colons (:) are included as separators because we don't want them to appear in result cells.

now, expand**separated by a mask**topper*text division*window, type the mask*input divider*box and click on*Division*:

The result will be similar to:

notes.Splitting a string with a mask is**case sensitive**. So make sure you enter exactly the same characters in the mask as in the original string.

One of the great advantages of this approach is flexibility. For example, if all raw strings have date values and you want them to appear in separate columns, use this mask:

** * Error: * Exception: **

Translated into plain English, the mask instructs the plugin to split the source string into 4 parts:

- All characters up to the first space found in the string (date)
- characters between 1
^{Yingshi}space and words*error:*(year) - text in between
*error:*I*exception:*(error code) - everything after
*exception:*(exception text)

I hope you enjoy this quick and easy way to split a table in Excel. If you want to try it out, you can download a trial copy below. Thanks for reading and we hope to see you on our blog next week!

## available for download

Split cell type in Excel(.xlsx file)

Ultimate Suite 14 day version with full features(.exe)

## Maybe you're right. . . I'm interested in

- How to split cells in Excel
- TEXTSPLIT function: A quick way to split cells according to separator
- How to unmerge cells in Excel
- How to separate names in Excel: Separate first and last names into different columns
- How to split date and time in Excel
- How to join two columns in Excel without data loss