how to split cells in excel 2008 for mac

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.

Split String Index: Split a String at a Specified Position Split a String at the First Occurrence of a Character Split a String at the Nth Occurrence of a Character Split a String at a Specified Position There are three built-in Excel functions that are designed for splitting a string at a specified position.

In the example below, the Left function returns the first two characters of the string test string: LEFT( test string, 2 ) returns the text string te 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: MID( test string, 6, 3 ) returns the text string str.

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: RIGHT( test string, 2 ) returns the text string ng Split a String at the First Occurrence of a Specified Character If you want to split an excel text string at the first occurrence of a specified character, (e.g.

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

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.

Split String at a Specific Character - Examples Example 1 - Return Text From the Beginning of a Text String, Up to the First Space 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.

Example 2 - Return Text From the End of a Text String 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.

Split a String at the Nth Occurrence of a Specified Character 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 Nth space One way to find the position of the Nth occurrence of a character is to use the Excel Substitute function, combined with the Excel Find or Search function.

The Substitute function substitutes the Nth 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.

Split String at the Nth Occurrence of a Specified Character - 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 textstring 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.

How To Split Cells In Excel 2008 For Mac