One perfect example of automatically cleaning up data in Excel is when working with name fields. You can separate first and last names (or actually any text with spaces) using this method.
Syntax
=LEFT(Full name, FIND(" ", Full name) – 1)
Steps
- Begin by typing in =LEFT(
- Select or type in the range reference that contains the full name (i.e. C3)
- Type in FIND(
- Add double quotes and a space as string " "
- Select or type in the range reference that contains the full name (i.e. C3)
- Type in ) to close the FIND function
- Type in -1
- Type in ) and press Enter to complete the formula
How
To extract the first part from a string, you need to find the start and end points in the full text to grab that section. The full text combination can be like [first name] [last name], where the data we're looking for is the first part of this string. Subsequently, the first name start index is going to be 1 and you can use the LEFT function to return the string by defining the number of characters.
=LEFT(B3,
The number of characters can be defined by adding a space (" ") that separates the first and the last names. Based on this, finding the index of the space will get us to the number of characters need. The FIND function will come to our aid by returning the index of a given string in another.
FIND(" ",B3)
Last step is eliminating the space and extracting the first part. The index 1 should be subtracted from the space character index number to get the first part only.
-1)
The final formula is,
=LEFT(B3,FIND(" ",B3)-1)
