How to Extract First Name in Excel

by

Sometimes when working with data such as employee or student records, your datasets will contain full names.

However, you may only need a specific part of the full name for further reporting and analysis purposes.

In this article, I will cover five simple ways to extract the first name from a full name in Excel.

Using the Flash Fill Feature

The Flash Fill feature can be used to recognize patterns quickly.

Once Flash Fill picks up a pattern, it will automatically populate a column from the input you supplied. It is available from Excel 2013 onwards.

In our example, column A has the full names, and we want to see only the corresponding first names in column B.

Names dataset where we need to extract first name

Here are the steps needed to extract the first name using the Flash Fill feature:

  1. Manually type only the first name from cell A2 in cell B2. This is the pattern which we would like Flash Fill to emulate.
Enter first result manually in cell B1
  1. Select cell B2.
Select cell B2
  1. Now go to the Data Tab. In the Data Tools Group, click on the Flash Fill icon.
Click on the Flash Fill icon
  1. Flash Fill sees that we want only the first name extracted and populates the rest of the column for us, accordingly.
first names extracted by Flash Fill

Caution: If you update column A, i.e., add another full name to column A, column B won’t automatically be updated. This is because Flash Fill is not a dynamic feature.

In addition, if Flash Fill doesn’t pick up the pattern (if you have situations where your pattern is quite complex), you may not get the results you desire. So it is advisable to always check your data.

Note: If you want to use a shortcut key instead, after selecting cell B2, press CTRL+E. This will also allow you to apply Flash Fill.

Also read: Extract Last Name in Excel

Using Text-to-Columns

The Text-to-Columns Feature allows you to split text strings from a single column into multiple columns.

You will have to specify a delimiter so that the wizard knows exactly where to split the text.

In our example, our full names in column A comprise first names, middle names, and last names. We want to ultimately isolate the first names in column B.

Full names dataset

Here are the steps needed to extract the first name using the Text-to-Columns Wizard:

  1. First, we need to select range A2:A8, which contains the full names.
Select the range containing the names
  1. With the range selected, go to the Data Tab. In the Data Tools Group, select the Text-to-Columns Wizard.
Click on Text-to-Columns
  1. The Text-to-Columns Wizard should appear. For Step 1, ensure the Delimited option is checked.
Select the Delimited option
  1. Click Next.
  2. For Step 2, since our first, middle, and last names are separated by a space, the space is our delimiter. So this option needs to be checked. Uncheck the Treat consecutive delimiters as one option.
Select space as delimiter and check Treat consecutive delimiters as one
  1. Click Next.
  2. In Step 3, you can select another Format if necessary. However, in this case, we are going to leave the default ‘General’ option as our choice. We will change the Destination to cell $B$2.
Select the General option and specify Destination

Note: If we don’t change the Destination cell, then our original values in column A will be overwritten.

  1. Click Finish. The first names, middle names, and last names have now been split into three separate columns.
Text-to-Columns result
  1. We don’t need the middle names and last names, so we can select these values and press Delete on our keyboard. So we are left with only our first names in column B.
Delete columns except one with first name
Also read: Reverse First and Last Name with comma

Using the TEXTBEFORE Function

The TEXTBEFORE Function is a new function that has been recently introduced in Microsoft 365.

It is used to return all the text that occurs before a specified delimiter. This function is quite easy to use and understand.

In our example, column A has the full names, and we want to see only the corresponding first names in column B.

Names dataset

Here are the steps needed to extract the first name using the TEXTBEFORE Function:

  1. Use the following formula in cell B2 and then press Enter:
=TEXTBEFORE(A2," ")
TEXTBEFORE function
  1. Fill the rest of the column by dragging the fill handle down.
dragging the formula down the column

Explanation of the formula

Let’s begin by familiarizing ourselves with the syntax of the TEXTBEFORE Function:

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
  • text – the entire text that the function will evaluate. You can directly enter text into the function using quotation marks or use a cell reference. This is a required value.
  • delimiter – the character or substring before which extraction should occur. This is a required value.
  • instance_num – the instance number of the delimiter. By default, this value is 1, which indicates the first occurrence of the delimiter. This value is optional.
  • match_mode – specifies whether the search is case-sensitive. It can only be 0 or 1. 0 indicates case sensitive and is the default value. This value is optional.
  • match_end – specifies whether Excel should treat the end of the text as a delimiter. It can only be 0 or 1. If 0, then Excel will not match the delimiter against the end of the text, this is the default. This value is optional.
  • if_not_found – tells Excel what to do if a match is not found. This value is optional. If not specified, then the #N/A error is returned.

In our formula above, Excel searches for the space within the text in cell A2, since this is the delimiter specified.

The space occurs directly after the first name. Once this position is located, all the characters before the delimiter are extracted.

This allows us to isolate the first name in cell B2.

Also read: How to Merge First and Last Name in Excel

Using the TEXTAFTER Function to Extract a First Name

We can extract the first name from a full name using the TEXTAFTER Function if we have a dataset where the last name is first.

The TEXTAFTER Function, like the TEXTBEFORE Function, is also only available in Microsoft 365.

In our example, column A contains the full names. However, the last names are first, with a comma and a space separating the first and last name.

Names dataset with comma

Here are the steps needed to extract the first name using the TEXTAFTER Function:

  1. Use the following formula in cell B2 and then press Enter:
=TEXTAFTER(A2,", ")
TEXTAFTER formula
  1. Fill the rest of the column by dragging the fill handle down.
Copy the formula down the column

Explanation of the formula

Let’s look at the syntax of the TEXTAFTER Function:

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
  • text – the entire text which the function will evaluate. You can directly enter text into the function using quotation marks or use a cell reference. This is a required value.
  • delimiter – the character after which extraction should occur. This is a required value.

In our formula above, Excel searches for the comma followed by the space within the text in cell A2, since this is the delimiter combination specified.

Once this position is located, all the characters following the delimiter combination are extracted in cell B2. 

Also read: Extract ZIP Code from Address in Excel

Using the LEFT and FIND Functions to Extract a First Name

You can use the older TEXT functions to extract the first name from a full name, if you don’t have Microsoft 365.

We can use the LEFT and FIND Functions in one formula to accomplish this task.

In our example, column A has the full names and we want to see only the corresponding first names in column B.

Full Names dataset

Here are the steps needed to extract the first name using the LEFT Function and the FIND Function:

  1. Use the following formula in cell B2 and then press Enter:
=LEFT(A2,FIND(" ",A2)-1)
LEFT and FIND function to extract first name
  1. Fill the rest of the column, by dragging the fill handle down.
Result of LEFT and FIND function

Explanation of the formula

The LEFT Function is used to return a specified number of characters from the left side of a text string.

The FIND Function on the other hand, returns the position of a certain character within a text string.

In order to understand our formula, we have to break it down into layers.

  • The innermost function

FIND(” “,A2)-1)

The FIND Function looks for the position of the space in the full name text in cell A2. Once it locates the position, 1 is subtracted from this number, in order to give us the number of characters just before the space.

  • The outer function

The LEFT Function then knows how many characters to extract from cell A2 starting from the left, to get only the first name because of the FIND Function. So eventually only the first name is returned in cell B2.

Also read: Separate Comma Separated Values in Excel

Using Power Query to Extract a First Name

Power Query is a powerful business intelligence application available from within the Excel interface. It will enable you to prepare, process and transform your data.

You can use Power Query to clean data or to do tasks such as separating or combining columns.

In our example, we have a list of full names and we want to extract the first name.

Full name in one cell dataset

Here are the steps needed to extract the first name using Power Query:

  1. The first step involves creating an Excel Table, since Power Query needs to be applied to an Excel Table.
  2. So select any cell in the range and press the CTRL+T shortcut on the keyboard. The Create Table Dialog Box should appear.
Create Table dialog box
  1. Check that the correct data range is shown and that the My table has headers option is checked.
  2. Click Ok to create the Table.
Data converted to an Excel table
  1. Now go to the Data Tab and in the Get & Transform Data Group choose From Table/Range.
Click on From Table/Range
  1. You should see the Table now loaded in the Power Query Editor.
Power Query Editor
  1. Now go to the Home Tab and in the Transform Group, select Split Column.
Click the Split Column option
  1. Select By Delimiter.
Click on the By Delimiter option
  1. You should see the Split Column by Delimiter Dialog Box.
Split Column by Delimiter Dialog Box
  1. Choose Space as the Delimiter. Check the ‘Each occurrence of the delimiter’ option.
  2. Click Ok.
  3. You should see that the single column has been split into two columns. The first column contains the first names, and the second column contains the last names.
names split in Two columns in Power Query
  1. We can now remove the column containing the last names. We do this by right-clicking on the column header and then choosing Remove.
Click the Remove option
  1. Right-click the first column header and select Rename. Enter the text First Names as the new header.
Rename the column
  1. Now go to the File Tab and select Close & Load.
Click on Close & Load
  1. You should now see only the First Names column.
First name column loaded into Excel
Also read: Remove Middle Name from Full Name in Excel

Using VBA to Extract a First Name

We can extract the first name from a full name using VBA. We can use the VBA LEFT Function and Instr Function to accomplish this task.

These are the VBA equivalents of the LEFT worksheet and FIND functions.

The VBA LEFT Function is used to extract characters from a text string starting from the left-hand side of a specified string.

The VBA Instr Function is used to return the position of a certain character in a specified text string.

Below, we have a column containing a list of full names.

Full names dataset
  1. So we start by selecting our range of interest first, which is range A2:A17.
Select all the cells with names
  1. Go to the Developer Tab and in the Code Group, click Visual Basic.
Click the Visual Basic option
  1. Go to the Insert Menu and select the Module item.
Click Insert and then click module
  1. Type the code below as a sub-procedure.
Sub Extracting_FirstName()

    ' Declare variables for the position of the space character and the extracted first name.
    Dim positionofSpace As String
    Dim firstName As String

    ' Loop through each cell in the selected range.
    For Each cell In Selection

        ' Find the position of the first space character in the cell's value (assumed to be the separator between the first name and the rest of the name).
        positionofSpace = InStr(cell, " ")

        ' Extract the first name by taking the characters to the left of the space.
        firstName = Left(cell, positionofSpace - 1)

        ' Write the extracted first name to the cell immediately to the right of the current cell.
        cell.Offset(0, 1).Value = firstName

    ' Continue with the next cell in the selected range.
    Next cell

End Sub
  1. To run the code, press F5 on the keyboard. The first names should be extracted in column B.
First names extracted in adjacent column
  1. Don’t forget to save the workbook as a Macro-enabled workbook.

In this tutorial, we covered how to use different methods to extract the first name from a full name.

We comprehensively went over the Flash Fill and Text-to-Columns methods. We then went over the use of TEXT functions to extract the first name.

Finally, we covered Power Query and VBA for simple automation of the first name extraction process.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment