Find out how to extract the first word in Excel in this quick and effortless Microsoft Excel tutorial.

Imagine trying to find the right tool in a cluttered garage—you know it’s there, but locating it quickly saves you time and hassle. The same applies to extracting information from a text. In Excel, pulling out just the first word from a long cell entry can feel like hunting through clutter. But what if there were a few simple ways to grab only the most relevant data?

Follow along with the methods outlined below to extract the leading word in Excel in various ways to meet different data analytics requirements.

Extract First Word Using Flash Fill

Flash Fill is the quickest way to find the first word of a text string and extract that to a destination cell. It works efficiently even if the destination cell or cell range isn’t adjacent to the source dataset.

It requires you to manually extract the first words of a few initial cells from the source dataset to the destination cell. Then, you can select the entire destination cell range and use the Flash Fill command to populate the first words of the rest of the source column.

If there are successive columns from which you want to copy the first words and place those in other columns, you can simply extract the first words manually in multiple destination columns. Then, use the Flash Fill command one by one. However, you can’t select multiple columns to use this Excell command.

Find above a sample database to practice this neat skill.

Let’s consider that you’d like to extract the first names and cities in two separate columns, named First Name and City, for the above dataset.

Go to the First Name column and select C2.

Type in the corresponding first name from A2.

Do the same for C3.

Now, go to the City column and select D2.

Type in the name of the city from B2 in D2.

Repeat this step for D3.

Now, select the C2:C10 cell range and click on the Fill drop-down menu in the Editing block.

Click on the Flash Fill command.

Perform the same steps for column D.

Excel will fill the remaining empty cells in columns C and D with the first words from cell ranges A4:A10 and B4:B10.

The only drawback of this method is it’s only available in Excel 2016 and later editions for Windows and Excel 2021 and newer editions for macOS.

Extract First Word Using Find and Replace

The Find and Replace tool is yet another basic Excel feature that can quickly eliminate all the words from a text string except the leading word. In this method, the changes you made are applied directly to the selected cell range. So, you must create a copy of the source data column in case you require the raw data for the future.

Suppose, you want to extract the subject names from the above dataset to remove clutter and make the table easily readable.

Select the source cell range, which is A3:A8.

Press Ctrl + H to launch the Find and Replace tool.

In the Find what field, enter the delimiter between two words, like a space, semi-colon, colon, etc., and an asterisk sign.

Keep the Replace with field empty.

Hit the Replace All button.

Microsoft Excel will keep the first word of the selected text strings and get rid of the remainder text.

It’ll also show the number of modifications it has made.

Extract First Word Using The LEFT Function

If you wish to use an Excel formula to extract the first word from a text string, you can use the LEFT function. It retrieves the first letter or letters in the selected text string, based on the number of characters you enter as the second argument of the function.

However, the LEFT function isn’t enough to complete this task. You’ll also need to use the FIND function that’ll help you select the first space in the text string. It’s assumed that the word to the left of the first space is the leading word.

There are two ways to use the LEFT function to retrieve the leading word in a text string. I’ve outlined both the methods below:

Formula for Space Delimiter

This technique is suitable for most text strings because those contain the character space as the word delimiter.

Your working dataset might look like the one shown above.

Let’s consider that you want to get the leading word, the first name, from the selected cell ranges and place those in the First Name column.

So, select C2 and type in the following formula into the cell:

=LEFT(A2, FIND(" ", A2 & " ") - 1)

You just need to change the cell reference in the above formula according to your own dataset.

Hit Enter to extract the first word.

Select C2 again, click on the fill handle square, and drag it down to extract the leading words for the rest of the cells.

Excel will automatically calculate the selected cells and extract the designated word.

Formula for Non-Space Separators

If the source text string contains a delimiter other than the space character, you’ll need to tweak the formula a bit.

For example, the city and state names in the column B of the above dataset are delimited with a comma.

To extract the first word from such data in the column D, cell D2, enter the following formula:

=LEFT(B2, FIND(",", B2 & ",") - 1)

Now, if your dataset contains any other delimiter, like a semicolon, colon, etc., put that character inside the double quotes of the above formula syntax.

Hit Enter to calculate D2 which extracts the first word from B2.

You can now use the fill handle in D2 and drag it down to populate the first words for the rest of the text strings.

Extract First Word When Only One Word Left

The LEFT and FIND function-based formula that you’ve used above looks up the first delimiter in the text string and then returns the entire content before that word separator.

If your text string contains no more than one word, then you need to improvise the formula so it doesn’t show any error.

For instance, in the dataset shown above, some of the entries in the Full Name column are just one word, probably just the first name.

If you wish to extract the first words from such a dataset, go to the destination cell, like C2, and enter the following formula into the cell:

=IF(ISNUMBER(FIND(" ", A2)), LEFT(A2, FIND(" ", A2)-1), A2)

You only need to change the cell address reference in the above formula to match your own dataset.

Hit Enter to get the first word.

Use the fill handle to copy and paste the formula to the rest of the cells.

Extract First Word Using Power Query

When you need to extract the first words from a large dataset with so many rows that Excel can’t handle, you can use Power Query. This tool also helps you to create external data connections between third-party database sources for automatic data importing and querying. You can use this method if you’re using Excel 2010 or newer editions.

For external data connectors, you can use the Get Data command in the Data tab. There, you’ll find data source options, like From Database, From Online Services, and so on.

In the current exercise, I’m showing you how to extract the first word in Excel Power Query by using the above dataset. It’s on an Excel worksheet.

To export this dataset, select it, and click on the From Table/Range command.

Click OK.

You’ll now see your source data inside the Power Query Editor interface.

Click on the Custom Column command inside the Add Column tab.

The Custom Column wizard will show up.

Enter a name, like Custom in the New column name field.

Into the Custom column formula field, enter the following formula:

Text.BeforeDelimiter([Department], " ")

Customize the above-mentioned Power Query M code so it works on your dataset. You mainly need to correct the column headers, like Department, or from whichever column you’d like to retrieve the first word.

Click OK to apply the formula and create a new column with the first words from the selected cells.

You should have successfully extracted the first word by now.

Click on the File tab and select Close & Load To from the context menu.

Now, you must select the Existing worksheet option in the Import Data dialog box.

Also, select the destination cell where you’d like to copy and paste the Power Query database.

Once you’ve imported the transformed dataset from Power Query, delete or hide the old dataset.

So, now you’ve got a new column with just the first words from the column A of the input dataset.

Conclusions

So far, you’ve learned and practiced various ways to extract the first word in Excel. These methods include Excel’s special commands, tools, functions, and Power Query.

Did the article help you to learn this amazing Excel skill? Comment below. If you also know of a better technique that I might have missed to list here, name it in your comment.