Sometimes, when you have the text data, you may want to extract the first word from the text string in a cell. There are multiple ways you can do this in Excel (using a combination of formulas, using Find and Replace, and using Flash Fill) In this tutorial, I will show you some really simple ways to extract the first word from a text string in Excel.
Extract the First Word Using Text Formulas
Suppose you have the following dataset, where you want to get the first word from each cell.
The below formula will do this:
Let me explain how this formula works. The FIND part of the formula is used to find the position of the space character in the text string. When the formula finds the position of the space character, the LEFT function is used to extract all the characters before that first space character in the text string. While the LEFT formula alone should be enough, it will give you an error in case there is only one word in the cell and no space characters. To handle this situation, I have wrapped the LEFT formula in the IFERROR formula, which simply returns the original cell content (as there are no space characters indicating that it’s either empty or has only one word). One good thing about this method is that the result is dynamic. This means that in case you change the original text string in the cells in column A, the formula in column B will automatically update and give the correct result. In case you don’t want the formula, you can convert it into values.
Extract the First Word Using Find and Replace
Another quick method to extract the first word is to use Find and Replace to remove everything except the first word. Suppose you have the dataset as shown below:
Below are the steps to use Find and Replace to only get the first word and remove everything else: The above steps would remove everything except the first word in the cells.
How does this work? In Find what field, we have used a space character followed by the asterisk sign. The asterisk sign (*) is a wild card character that represents any number of characters. So when we ask Excel to find cells that contain space character followed by the asterisk sign and replace it with blank, it finds the first space character and removes everything after it – leaving us with the first word only. And in case you a cell that has no text or only one word with no space characters, the above steps would not make any changes to it.
Extract the First Word Using Flash Fill
Another really simple and fast method to extract the first word using Flash Fill. Flash Fill was introduced in Excel 2013 and is available in all the versions after that. It helps in text manipulation by identifying the pattern that you’re trying to achieve and fills it for the entire column. For example, suppose you have the below dataset and you want to only extract the first word.
Below are the steps to do this: The above steps would fill all the cells with the first word from the adjacent column (column A). Note: When typing the expected result in the second cell in column B, you may see all text in all the cells in a light gray color. That is the result you will get if you hit the enter key right away. In case you don’t see the gray line, use the Flash Fill option in the ribbon. So these are three simple methods to extract the first word from a text string in Excel. I hope you found this tutorial useful! Other Excel tutorials you may like:
Extract Numbers from a String in Excel (Using Formulas or VBA) How to Extract a Substring in Excel (Using TEXT Formulas) Separate First and Last Name in Excel (Split Names Using Formulas) How to Remove Comma in Excel (from Text and Numbers) How To Remove Text Before Or After a Specific Character In Excel How to Combine First and Last Name in Excel Remove Characters From Left in Excel Separate Text and Numbers in Excel
