ExcelFunctions.net

Search Site:

Related Page:

Excel String Concatenation
If you want to split a text string in Excel, there are three functions that are built in to Excel for this purpose. These are the Excel Left function, the Mid function and the Right function. These functions are described below:

The Excel Left function returns a specified number of characters from the left (the beginning) of a supplied text string. For example,

The Excel Mid function returns a specified number of characters from the middle of a supplied text string, beginning at a specified character. For example,

The Excel Right function returns a specified number of characters from the right (the end) of a supplied text string. For example,

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 Excel Find and Search functions are useful for finding the position of a character (or string of characters) within in an initial supplied string. The only difference between the two functions is that the Find function is case-sensitive, whereas the Search function is not.

Another function that may be useful for splitting a string at the first occurrence of a specified character is the Excel Len function, which returns the length of a supplied text string. This is illustrated in the following examples.

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 ) returns the value 6 (i.e. 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. Therefore, if you use this function to substitute the N'th occurrence of a character, with a different character (ensuring that this new character does not occur anywhere else in the original text string), you can then use the Find function, to return the position of this and supply this value to the Left, Mid or Right function. This is illustrated in the following example.

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:

Formula broken 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 above formula, we have substituted the third space with the character "|". This character has been used, as we know that 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:

A single formula:

A | ||
---|---|---|

1 | An example text string | |

2 | =LEFT( A1, FIND( "|", SUBSTITUTE( A1, " ", "|", 3 ) ) - 1 ) | - returns the result "An example text" |