Summary

To extract the first name from a full name in <first> <middle> <last> format, you can use the TEXTBEFORE function. In the worksheet shown, the formula in cell D5 is:

=TEXTBEFORE(B5," ")

As the formula is copied down, it returns the first name from each name listed in column B.

Note: As of this writing, the TEXTBEFORE function is only available in Excel 365. See below for an alternative formula that will work in older versions of Excel.

Generic formula

=TEXTBEFORE(name," ")

Explanation 

In this example, the goal is to extract the first name from names that appear in <first> <middle> <last> format, where the middle name is optional. The easiest way to do this is with the newer TEXTBEFORE function. In older versions of Excel, you can use an alternative formula based on the LEFT function and the FIND function. Both formulas are explained below.

Note: this formula does not account for titles (Ms., Mr., Dr., etc.) in the full name. If titles exist, they should be removed first.

Modern solution

In the current version of Excel, the TEXTBEFORE function is the simplest way to solve this problem. The TEXTBEFORE function extracts text that occurs before a given delimiter. In its simplest form, TEXTBEFORE only requires two arguments, text and delimiter:

=TEXTBEFORE(text,delimiter)

In this problem, the text we want to work with comes from the names in column B, and the delimiter is a single space (" "). To extract the first name from each full name, the formula in cell D5 looks like this

=TEXTBEFORE(B5," ")

As the formula is copied down the column, TEXTBEFORE returns the text before the first space character that appears in each name. The TEXTBEFORE function has a lot of options which are explained on this page.

Legacy Excel

In older versions of Excel that do not offer the TEXTBEFORE function, you can use an alternative formula that looks like this:

=LEFT(B5,FIND(" ",B5)-1)

Working from the inside out, the FIND function finds the first space character (" ") in the name and returns the position of that space in the full name. The number 1 is subtracted from this number to account for the space itself. The LEFT function uses this number as the total number of characters that should be extracted in the next step below.

Back in the worksheet, the first space (" ") in  cell B5 occurs as the 6th character, so FIND returns 6:

=FIND(" ",B5) // returns 6

After 1 is subtracted, we have 5, which is returned to the LEFT function as num_chars:

=LEFT(B5,5) // returns  "Emily"

The LEFT function then extracts 5 characters starting at the left and returns "Emily" as a final result.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.