What can you do to split text from one column into two? Here's how to tackle the process using an Excel formula.
Is there a formula that can split text?
Providing there is a particular rule that enables you to split up the text, then it can usually be done via a formula. For example, if there is a comma between the first and last names or if there is a dash between the sections of a product code, then you can use a formula to extract the parts.
Figure 1 has an example where the full name needs to be split into first and last names. A comma separates the two names. The function that allows flexible splitting is the SEARCH function. The SEARCH function returns a number that represents the character position of the text being searched for within another text string. It will return an error if the text is not in the text being searched.
Find_Text is usually keyed in and is usually a single character. Text must be surrounded by quotation marks.
Within_Text is usually a cell reference.
Start_Num is optional. If omitted the search starts from the first character. This number is used if there are multiple search characters within the text and you need to identify the second or third instance.
The SEARCH function is not case sensitive. If you need to do a case sensitive search, use the FIND function. The FIND function has the same arguments as SEARCH, but it performs a case sensitive search.
To extract the last name (shown first in cell A2) we need to use the LEFT function. The LEFT function extracts a number of characters from the left of a text string:
Text is usually the cell reference of the text string to extract from.
Num_Characters is optional. If omitted, the first character is extracted from Text. This number specifies the number of characters to extract from the left. If the number exceeds the number of characters in the text string, then the whole text string is returned.
The formula in cell C2 that extracts the last name from cell A2 is:
In this LEFT function, cell A2 contains the Text to extract from and the Num_Characters argument is supplied by the result of the SEARCH function, less one. One is subtracted from the SEARCH result because the comma is not required in the last name. In this case the SEARCH function returns 6, as the comma is the sixth character in cell A2. We only want the first 5 characters, so 1 is deducted.
If there could be a space before the comma, then you could use:
The TRIM function removes any leading or trailing spaces from the text. Any spaces within the text are not changed.
To extract the first name from cell A2 the formula in cell B2 is:
The RIGHT function works exactly like the LEFT function, except that it extracts text from the right of the text. To calculate how many characters to extract from the right in cell A2, we can subtract the result of the SEARCH function from the number of characters in cell A2.
The LEN function returns the total number of characters in a text string.
Note: a space is treated as a character. So LEN(A2) returns 11 as there are 11 characters in cell A2, including the space. The SEARCH function returns 6. The RIGHT function uses 5 (11 - 6) as the number of characters to extract. The first name is only four characters long in this case and the TRIM function will remove any extra spaces.
In our example, cell A3 does not have an extra space after comma. Figure 2 shows the extracted names.
Handling SEARCH function errors
As mentioned earlier, if the text being searched for is not in the text string then the SEARCH function returns an error. In our example, if there was a single name in column A without a comma, then both our formulas would return an error as can be seen in Figure 3.
We can use the IFERROR function to handle these errors. The IFERROR function was introduced in Excel 2007 and provides a simple way to handle all of Excel’s errors. The IFERROR function will return a value if it is not an error. If the value is an error then another value is displayed:
Value is typically a formula. The result of the formula will be displayed if it is not an error.
Value_If_Error is what to display if an error is encountered in Value. This can be a cell reference, text entry, zero or another formula.
To amend our formulas to handle single word names, we can change the formulas for row 2 as shown below. These can be copied down the columns.
Cell B2 becomes: =IFERROR(TRIM(RIGHT(A2,LEN(A2)-SEARCH(",",A2))), A2)
If an error is encountered, this assumes the name in A2 is to be used as the first name.
Cell C2 becomes: =IFERROR(LEFT(A2,SEARCH(",",A2)-1) , "")
If an error is encountered, a blank cell is displayed. Two quotation marks, "", defines a blank cell in Excel.
There is also a non-formula solution to this problem of splitting text, called Text to Columns. It is in the Data ribbon tab and was discussed in the October 2012 issue of INTHEBLACK.
Try the sample spreadsheet
Neale Blackwood CPA is CPA Australia’s Excel expert. He runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected].