Separate First name from Last name using SEARCH in Excel
In this example, we will be demonstrating how to use the SEARCH Function to separate first and last names. In more detail, we will be using the SEARCH Function to determine the space between parts of a text string. In our example, both first and last names will be listed in Column A and we will copy all the first names to Column B. Here is the syntax that must be used for the SEARCH Function:
SEARCH(find_text, within_text, start_num)
find_text: The text or character for which you are searching. Wildcard characters, question marks (?), and asterisks (*) can be used in find_text. A question mark matches any single character, and an asterisk matches any sequence of characters. To find a question mark or asterisk, type a tilde (~) before the character.
within_text: The text you want to search for find_text.
start_num: The start position for the search function within text; if there is no start_num defined inside the function, Excel sets it to 1.
How to separate first name from last name:
- Open a new worksheet and enter a series of full names as shown below in cells A2:A10.
- Select cells B2:B10 and type the following formula: =LEFT(A2,SEARCH(” “,A2)-1).
- Press CTRL+Enter.
To separate the last name from the first name, we will need to use the MID Function. Here is the syntax that we must use:
MID(text, start_num, num_chars)
text: Text string containing the desired characters.
start_num: Position of the first character to extract from the
text.
num_chars: Number of characters to be extracted.
In Cell C2, enter the following formula to separate the last name: =MID(A2,SEARCH(” “,A2)+1,100) and then use the fill handle to copy the formula to the other cells in the column.
