How to Use Excel to Find the First Word in a Text String (3 Easy Ways)

You may slice and dice the text data with the assistance of some fantastic algorithms in Excel.

When you have text data, you might occasionally wish to get the first word out of a text string in a cell.

In Excel, there are several approaches to accomplish this (using a combination of formulas, using Find and Replace, and using Flash Fill)

In this lesson, I’ll demonstrate several straightforward methods for obtaining the first word from a text string in Excel.

This instruction explains: 1. Using the (LEFT/FIND) Text Formulas 2. Using the wild character technique finding and replace 3. Flash Filling (Fastest and Easiest Method)

1. Using the (LEFT/FIND) Text Formulas

Assume you wish to extract the first word from each cell in the following dataset.

This may be accomplished using the formula below:

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

Let me describe the operation of this formula.

To locate the space character in a text string, utilize the formula’s FIND component. The LEFT function is used to extract all the characters in the text string that come before the first space character after the formula locates the location of the space character.

Even while using only the LEFT formula should be sufficient, it will give you an error if the cell has only one word and no space characters.

As there are no space characters indicating that the cell is either empty or contains only one word, I have wrapped the LEFT formula with the IFERROR formula to handle this case. This formula simply returns the original cell content.

The dynamic nature of the outcome is one advantage of this strategy. As a consequence, if you alter the original text string in any of the cells in column A, the formula in column B will update immediately and provide the desired outcome.

You can turn it into values if you don’t want the formula.

 

2. Using the wild character technique finding and replace

Using Find and Replace to exclude everything but the first word is another easy way to find the first word.

Assume you possess the dataset depicted below:

The steps to utilize Find and Replace to just keep the first word and eliminate the rest are as follows:

Column A to column B text should be copied. This is to ensure that we too have access to the original data.

Choose every cell in Column B where you want the initial word by selecting it.

1. On the Home tab
2. Select the Find and Select option under the Editing group, then select Replace. The Find & Replace dialogue box will then be shown.
3. Enter * in the Find What box (one space character followed by the asterisk sign)
4. Fill in nothing in the Replace with the field.
5. Select Replace All from the menu.

 

The first word in the cells would remain after doing the aforementioned processes.

The Find and Replace dialogue box may also be opened by pressing the keyboard shortcut Control + H.

How does that function?

We have utilized the space character and the asterisk symbol in the Find what field. A wildcard character that may stand in for any number of characters is the asterisk symbol (*).

Therefore, Excel identifies the initial space character and eliminates everything that comes after it, leaving us with the first word only, when we ask it to discover cells that have a space character followed by an asterisk sign and replace it with a blank.

The aforementioned methods would not affect a cell that has no text or just one word without any spaces.

3. Flash Filling (Fastest and Easiest Method)

Using Flash Fill is another really quick and easy way to extract the initial word.

Flash Fill was first introduced in Excel 2013 and is included in every update afterward. By recognizing the pattern you’re attempting to establish and filling it for the full column, it aids with text manipulation.

For instance, let’s say you simply want to extract the first word from the dataset below.

The procedures are as follows:

Enter “Marketing” by hand in cell B2, the adjacent column in our data (which is the expected result)

Enter “HR” in cell B3. Choose the B2 through B10 range.

Select the Home tab.

Click on the Fill drop-down in the Editing group.

Select Flash Fill from the menu.

The first word from the adjacent column would be entered into each cell using the aforementioned processes (column A).

Caution:

Flash Fill often functions well and produces accurate results, although occasionally this may not be the case. Just be sure to confirm that the outcomes match your expectations.

Note: You might notice that all of the text in all of the cells is a light grey hue when you type the desired outcome in the second cell of column B. You will receive such outcome if you immediately press the enter key. Use the Ribbon’s Flash Fill option if you can’t see the grey line.

These are the three straightforward techniques for finding the first word in an Excel text string.

I sincerely hope this instruction was helpful.