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

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 theItem-color-sizepattern and you want to split the column into 3 separate columns:

1. Around theItem 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).
   

2. Around thecolour(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 numberis the position of the first hyphen +1:

     LOOKUP("-",A2) + 1

   • Number of characters to extract: the difference between the position of the 2^zdhyphen and the 1^sthyphen, minus 1:

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

3. Around themaat(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^zdhyphen. The difference is the number of characters after the 2^zdhyphen, 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 separatorroom(" "),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 theItem name:

  =LEFT(A2,LOOKUP(CHAR(10),A2,1)-1)

• Around thecolour:

  =CENTRE(A2, LOOKUP(CHAR(10),A2) + 1, LOOKUP(CHAR(10),A2,LOOKUP(CHAR(10),A2)+1) - LOOKUP(CHAR(10),A2) - 1 )

  See Also

  Fix excel numbers that are not correctKuinka laskea ehdollisesti muotoiltuja soluja Excelissä?Kuinka laskea ehdollisesti muotoiltuja soluja Excelissä?

• Around themaat:

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

Split string of 'number + text' pattern

If you split cells where text appears after number, you canextract numberswith 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 textby 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:

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.

Split cells by character

Choose this option when you want to split the cell contentsevery time the specified character occurs.

For this example, let's take the strings of theItem-color-sizepattern 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:

1. Assuming you haveUltimate sequelinstalled, select the cells you want to split and click on itSplit texticon on theAblebits datatab.
   
2. OfSplit textThe pane opens on the right side of your Excel window and you do the following:
   • Fold theSplit by charactergroup and select one of the predefined separators or type another character in theAmendedbox.
   • Choose whether to split cells into columns or rows.
   • View the result belowExamplesection and click theDivideknob.

Tip.If there can be more than one consecutive delimiter in a cell (for example, more than one space), select theTreat consecutive separators as onebox.

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 usingany combination of charactersas 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 theSplit by stringsgroup 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 aroombefore and afterInInofto 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:20
5.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 charactersSplit by stringsbox:

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

Press on theDividebutton, and you immediately get the result:

Split cells by mask (pattern)

To separate a cell by a mask means to split a stringbased 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 itSplit by masksection about theSplit texttype the mask in itEnter delimitersbox and clickDivide:

The result looks something like this:

Remark.Split string by mask iscase 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^stspace and the wordWRONG:(time)
• Text in betweenWRONG:InException:(error code)
• Everything that comes afterException:(exception text)