How to use the SEARCH function to separate first name from last name in Excel

In this article, we will learn how to use the SEARCH function in Excel to separate first name from last name in a text string. This is a useful skill when you have a list of names that are not separated by a delimiter, such as a comma or a space, and you want to split them into two columns.

The SEARCH function in Excel is a text function that returns the position of a substring or a character within another text string. The syntax of the SEARCH function is:

=SEARCH(find_text, within_text, [start_num])

• find_text is the text you want to find within another text string. You can use wildcard characters, such as ? and *, to match any single character or any sequence of characters. If you want to find an actual ? or *, you need to use a tilde (~) before the character.
• within_text is the text in which you want to search for find_text.
• [start_num] is an optional argument that specifies the character number in within_text at which you want to start searching. If omitted, it is assumed to be 1.

The SEARCH function is not case-sensitive, meaning it does not distinguish between uppercase and lowercase letters when searching text. For example, =SEARCH("a", "Apple") and =SEARCH("A", "apple") will both return 1.

Procedures

To use the SEARCH function to separate first name from last name in Excel, we need to follow these steps:

• Identify the character or substring that separates the first name from the last name in the text string. For example, if the names are in the format of “LastnameFirstname”, then the separator is the first letter of the first name.
• Use the SEARCH function to find the position of the separator in the text string. For example, =SEARCH("?", "SmithJohn") will return 6, which is the position of the first letter of the first name.
• Use the LEFT function to extract the left part of the text string up to the separator, which is the last name. For example, =LEFT("SmithJohn", 6-1) will return “Smith”.
• Use the RIGHT function to extract the right part of the text string from the separator, which is the first name. For example, =RIGHT("SmithJohn", LEN("SmithJohn")-6) will return “John”.
• Copy and paste the formulas to the rest of the cells that contain the names.

Example

Let’s see an example of how to use the SEARCH function to separate first name from last name in Excel. Suppose we have a list of names in column A that are not separated by a delimiter, such as this:

We want to split the names into two columns, one for the first name and one for the last name. To do this, we can use the following formulas in column B and C:

The result will look like this: