Excel: Split string by separator or pattern, separate text and numbers (2023)

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 SKUsProduct-Color-Sizeshape and you want to split the column into 3 separate columns:

1. extractName 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).
   

2. extractcolour(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 numberis the position of the first dash +1:

     search("-",A2) + 1

   • the number of characters to export: Difference between position 2^ndhyphen and 1^YingshiCrtica, minus 1:

     search("-", A2, search("-",A2)+1) - search("-",A2) -1

3. extractSize(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^nddash. The difference is in the number of characters after 2^nddashes, 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.

• extractName of the product:

  =Left(A2,Search(Character(10),A2,1)-1)

• extractcolour:

  =MIDD(A2, SEARCH(CHARACTER(10),A2) + 1, SEARCH(CHARACTER(10),A2,SEARCH(CHARACTER(10),A2)+1) - SEARCH(CHARACTER(10),A2) - 1 )

  See Also

  How to fix #VALUE! errorExcel IF function with multiple conditionsExcel TEXT functions with example formulas

• extractSize:

  =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.

Split a series of "Number+Text" patterns.

If you want to split cells where text appears after a number, you canextract the numbersUse 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 textSubtracting 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:

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.

Separate cells by character

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

For this example, let's put in a tableProduct-Color-SizeThe 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:

1. suppose you haveGreat apartmentAfter installation, select the cell you want to split and clicktext divisionicon enabledAbility dataLet me underline.
   
2. thistext divisionA window will open on the right side of the Excel window where you can:
   • expanddivided by charactergroup and select one of the predefined separators orcustomizedBox.
   • Choose whether to divide the cells into columns or rows.
   • See the results belowpreviewsection and then click onDivisionbutton.

advice.If there are many consecutive separators in the cell (for example, many spaces), selectTreat consecutive delimiters as oneBox.

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 usingany combination of charactersas 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 expanddivided by a stringgroup 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 includespacebefore and afterIItheto avoid splitting words.

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

1.5.2016 12:20
5.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, usedivided by a stringBox:

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

to winDivisionand you will immediately get the result:

Split cells by mask (pattern)

Splitting cells with a mask means splitting the arraybased 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, expandseparated by a masktoppertext divisionwindow, type the maskinput dividerbox and click onDivision:

The result will be similar to:

notes.Splitting a string with a mask iscase 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^Yingshispace and wordserror:(year)
• text in betweenerror:Iexception:(error code)
• everything afterexception:(exception text)