There were countless times when I had a list of full names, and all I needed was the First Name. It would be time-consuming to get the first names one by one.
Thank goodness there are formulas for Excel extract the first name from full name to make my life easier!
In this article, you will be provided with a detailed guide on how to extract first name in Excel:
Let’s explore each of these methods to extract first name from full name in Excel one-by-one.
Using Find and Left formula
It’s very easy to do Excel extract first name with the LEFT and FIND formula!
Here is the gameplan:
- Use the FIND formula to find the location of the space or any other delimiter that separates the First Name and the Last Name
- However, we need to deduct this numerical location by 1, so that we have the location of the end of the First Name
- With this number, we will use the LEFT formula to retrieve the First Name!
Now, it is essential to understand how these functions work to be able to use them to Excel extract first name.
The FIND Formula is used to find the position of the specified text within a cell.
The syntax of FIND formula is:
=FIND(find_text, within_text, [start_num])
- find_text: It is the text you want to find. Here, we want to find space. So, the argument will be a blank within quotes.
- within_text: It is the cell containing the text you want to find.
- start_num: It specifies the character at which you want the search to start. This is an optimal argument. If left blank, Excel will start the search from the 1st character itself.
The LEFT Formula is used to extract the specified number of characters from the start of a text string.
The syntax of the TEXT formula is:
- text: It is the text string from which you want to extract characters
- num_chars: It is the number of characters you want to extract.
Moving on, let’s combine these two formulas to Excel extract first name from full names.
Watch it on YouTube and give it a thumbs-up!
Follow the step-by-step tutorial below on how to get first name in Excel and make sure to download the Excel workbook to follow along:
DOWNLOAD EXCEL WORKBOOK
STEP 1: We need to enter the LEFT function and select the Full Name:
STEP 2: We need to enter the FIND formula to get the empty space located between the first and last name:
=LEFT(C7, FIND(” “
STEP 3: Select the Full Name again for the FIND formula’s 2nd argument:
=LEFT(C7, FIND(” “, C7)
STEP 4: Deduct 1 from the FIND formula so that our result will return us the text up to the last letter of the first name:
=LEFT(C7, FIND(” “, C7) -1)
STEP 5: Do the same for the rest of the cells by dragging the formula all the way down using the left mouse button.
Now you are able to extract all the First Names from your FULL NAME using the FIND & LEFT formula in Excel!
Using Text to Column feature
A quick and easier way to Excel extract first name is to use the Text to Column function!
All you need to know is first the delimiter between the first and last name. Here, the first and last name is separated by a COMMA (,).
This function will get the first and last names split into two separate columns. Get this done by following the steps below:
STEP 1: Select the cells containing the full names.
STEP 2: Go to Data > Text to Column.
STEP 3: In the Convert Text to Columns wizard, Select Delimited Option and Click Next.
A snapshot of your selected data is displayed at the bottom of the wizard.
STEP 4: Select one or more delimiter applicable for your data and Click Next. Here, we need to select the delimiter – comma.
If your data contained both comma and space as a separator, you can select both!
STEP 5: In this step, you can select the location where you want the output to be displayed. Click on the small arrow next to the destination to insert your destination.
STEP 6: Select cell D7 and click on the arrow again.
STEP 7: Click Finish.
And, it’s done! The first and last names are now separated and displayed in two separate columns.