This page describes how to split a string in Excel using Excel's Left, Mid and Right functions.
There are three built-in Excel functions that are designed for splitting a string at a specified position. These are the Excel Left, Mid and Right functions. These functions are each described below:
The Excel Left function returns a specified number of characters from the left (the beginning) of a supplied text string.
In the example below, the Left function returns the first two characters of the string "test string":
The Excel Mid function returns a specified number of characters from the middle of a supplied text string, beginning at a specified character.
In the example below, the Mid function returns 3 characters from the middle of the string "test string", starting from character number 6:
The Excel Right function returns a specified number of characters from the right (the end) of a supplied text string.
In the example below, the Right function returns the last two characters of the string "test string":
If you want to split an excel text string at the first occurrence of a specified character, (e.g. at the first space), there is no built-in Excel function to do this. However, you can perform this task using the Left, Mid or Right functions, combined with other built-in Excel functions.
The other Excel functions that you may find useful when splitting a string at a specified position are:
| Find | - | Returns the position of a sub-string within a supplied string (case-sensitive). |
| Search | - | Returns the position of a sub-string within a supplied string (not case-sensitive). |
| Len | - | Returns the length of a supplied text string. |
Note that the only difference between the Find and Search functions is that the Find function is case-sensitive, while the Search function is not.
If you want to use a formula to split a text string at the first space, and then return the left part of the split string, this can be done by combining the Left function with the Find function. This is shown in the example below:
| A | B | ||
|---|---|---|---|
| 1 | test string | =LEFT( A1, FIND( " ", A1 ) - 1 ) | - returns the result "test" |
In the above formula, the Find function returns the value 5 as the position of the space within the supplied text "test string". Subtracting 1 from this value gives the value 4, which is then supplied to the Left function.
If you want to use a formula to split a text string at the first space, and then return the right (the end) part of the string, this can be done by combining the Right function with the Excel Find function and the Excel Len function. This is shown in the example below:
| A | B | ||
|---|---|---|---|
| 1 | test string | =RIGHT( A1, LEN( A1 ) - FIND( " ", A1 ) ) | - returns the result "string" |
In the above formula, the Len function returns the value 11, as the length of the string "test string" and the Find function returns the value 5 as the position of the space.
Therefore, the expression LEN( A1 ) - FIND( " ", A1 ) evaluates to 6 (= 11 - 5), which is then supplied to the Right function.
Therefore, the Right function returns the last 6 characters of the supplied string.
The problem with the Excel Find and Search functions is that they can only be used to find the first occurrence of a specified character (or string of characters), after a specified start position. So what can you do if you want to split your string at the N'th space?
One way to find the position of the N'th occurrence of a character is to use the Excel Substitute function, combined with the Excel Find or Search function.
The Substitute function substitutes the N'th occurrence of a specified string, with a second supplied string. The Find function can then be used to return the position of your substitute string, and this position can then be supplied to the Left, Mid or Right function.
An example of this is provided below.
In this example, we return the left part of the original text string "An example text string", up to the third space. For clarity, we will first break the formula down into 3 stages:
| A | ||
|---|---|---|
| 1 | An example text string | |
| 2 | =SUBSTITUTE( A1, " ", "|", 3 ) | - returns the result "An example text|string" |
| 3 | =FIND( "|", A2 ) | - returns the result "16" |
| 4 | =LEFT( A1, A3 - 1 ) | - returns the result "An example text" |
In the first stage of the above formula, we have substituted the third space with the character "|". The reason for choosing this character is that we know it does not occur in the original text.
The three stages shown in cells A2 - A4 of the above spreadsheet above return the left part of the original text string, up to the third space. If you are confident with Excel formulas, you may prefer to combine these three stages into a single formula, as shown below:
| A | ||
|---|---|---|
| 1 | An example text string | |
| 2 | =LEFT( A1, FIND( "|", SUBSTITUTE( A1, " ", "|", 3 ) ) - 1 ) | - returns the result "An example text" |