You may sometimes need to remove the first word from Excel sheets to clean up the data. Also, removing the first word may be needed to prepare the data for further processing. Excel offers various methods to solve this issue. You can either remove the first word with the VBA code or use the other Excel features. In this article, I will show you four different ways to remove the first word from the cells in Excel.
In this dataset, column A has some random texts. Now, I will present the results in column B after removing the first word from column A.
Let me show you how to remove the first word in Excel.
Remove the First Word in Excel Using the VBA Code
Using the VBA code is very fast to remove the first word from Excel sheets. I used the MID function to extract the text from the cell starting from the position after the first space character.
Here is the VBA code to apply to remove the first word from an Excel sheet:
Dim cel As Range
For Each cel In Selection
If InStr(cel, " ") > 0 Then
cel.Value = Mid(cel.Value, InStr(cel.Value, " ") + 1)
Steps to remove the first word in Excel using VBA:
- Right-click on the sheet tab.
- Then select View Code.
- In the Visual Basic Editor, select Insert > Module.
- After opening the Module, copy the following code.
- Paste the code in the Module.
- Go to Run tab> Run Sub option.
- Close the Visual Basic Editor.
- In the Excel workbook, go to the Developer tab.
- Now, select the cell range A2:A10.
- Then click on Macros from the Code group.
- In the Macro dialog box hit the Run button.
The first word from the text strings is removed with the simple VBA code this way. Here is the final result:
- Press ALT + F11 to open the Visual Basic Editor window.
- Press F5 or Fn+F5 to run the code.
Flash Fill Feature to Remove the First Word in Excel
The Flash Fill tool is really smart. Just manually type the input in the first cell, and Flash Fill will copy the pattern from the first cell and fill the whole column for you. I find this method as easy as the previous one.
To remove the first word using the Flash Fill command in Excel:
- In cell B2, manually write the entire text from cell A2 except for the first word.
- Then go to the Data tab.
- Now, click on the Flash Fill icon from the Data Tools group.
This method works well to remove the first word in Excel. Yet to avoid any small mistakes, you should manually go over the cells after applying the Flash Fill.
- 6 Ways to Remove the First 2 Characters in Excel
- 6 Ways to Remove the First 4 Characters in Excel
- Remove Non-Printable Characters in Excel [5+ Methods]
Remove the First Word in Excel Using RIGHT, LEN, & FIND Functions
=RIGHT(A2, LEN(A2) - FIND(" ",A2))
- The FIND function first detects the position of the <space> between the words in cell A2.
- The LEN function identifies the length of characters in cell A2. Then the position of the <space> is subtracted from the total length of cell A2.
- The RIGHT function then returns the resulting length of the right side of the text string of cell A2.
To remove the first word using the Flash Fill command in Excel, follow this guide:
- Select a cell.
- Write this formula: =RIGHT(A2, LEN(A2) – FIND(” “,A2))
- Press the ENTER key to apply the formula.
- Next, drag the Fill Handle icon to copy the formula in the cell range B2:B10.
This will update the Excel sheet by removing the first word from the text string.
Similarly, you can use the LEFT function to remove any word from the text string and return words from the left side of the cell.
- 7+ Methods to Remove Characters from Left in Excel
- 5 Approaches to Remove Characters from the Right in Excel
- 3+ Ways to Remove Dashes in Excel
Remove the First Word in Excel Using Find and Replace
Another amazing technique to remove the first word from the cells in Excel is using the Find and Replace feature.
I am using a different example dataset to show you this method.
Notice, here all the cells in column A have two words except the last two cells. There are three words inside cells A9 and A10. Therefore, the method will not successfully work for cells A9 and A10.
Steps to remove first word in Excel using the Find and Replace tool:
- First, select the targeted cell range A2:A10.
- Then press CTRL+H. This will bring the Find and Replace dialog box onto the screen.
- Go to the Replace tab.
- Then, type an asterisk and <space> “* ” in the Find what box.
- Now keep the Replace with box empty.
- Click on the Replace All button.
A message box will pop up on the screen.
- Click on the OK button.
The Find and Replace feature has removed the first word from all the selected cells of the dataset. However, in cells A9 and A10, the two words from the start have been removed. For this reason, this method is only good for removing the first word if the cell contains two words.
I hope this article helped you remove the first words in Excel. You can use any method you prefer, and the outcome will be similar. No matter which way you do it, make sure it achieves your desired result. And leave a comment in the comment section if you have any questions or concerns. Also, please visit our blog page to learn more about Excel. Thank you!
Frequently Asked Questions
How do I remove one word from a cell in Excel?
To remove one word from a cell in Excel, you can use a combination of functions. Assuming the word you want to remove is at the beginning of the cell, you can use the following formula: =TRIM(MID(cell_reference, LEN(word_to_remove) + 1, LEN(cell_reference)))
Replace “cell_reference” with the reference to the cell containing the text, and “word_to_remove” with the word you want to remove.
This formula uses MID to extract the portion of the text after the specified word and TRIM to remove any leading or trailing spaces. Adjust the formula based on the position of the word within the cell if it’s not at the beginning.
How do you remove the first letter of a word in Excel?
To remove the first letter of a word in Excel, you can use the MID function. Here’s a formula you can use: =MID(cell_reference, 2, LEN(cell_reference)-1)
Replace “cell_reference” with the reference to the cell containing the word. This formula extracts the portion of the text starting from the second character, effectively removing the first letter. Adjust the cell reference accordingly for your specific case.