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.
Introduction to the Dataset
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.
Easiest Way to Remove the First Word in Excel
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.
Step_1: Right-click on the sheet tab.
Step_2: Then select View Code.
This will bring the Visual Basic Editor to the screen.
Step_3: Now go to Insert>Module.
Step_4: After opening the Module, copy the following code.
Sub RemoveFirstWord() Dim cel As Range For Each cel In Selection If InStr(cel, " ") > 0 Then cel.Value = Mid(cel.Value, InStr(cel.Value, " ") + 1) End If Next cel End Sub
Step_5: Paste the code in the Module.
Step_6: Go to Run> Run Sub.
Step_7: Close the Visual Basic Editor.
This will bring you back to the Excel workbook.
Step_8: Next, go to the Developer tab.
Step_9: Now, select the cell range A2:A10.
Step_10: Then click on Macros from the Code group.
Now you will see the Macro dialog box on the Excel sheet.
Step_11: Then hit the Run button.
Final Result >
The first word from the text strings is removed with the simple VBA code this way.
Alternative Way #1: Use 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 easiest as the previous one.
Step_1: In cell B2, manually write the entire text from cell A2 except for the first word.
Step_2: Then go to the Data tab.
Step_3: Now click on the Flash Fill icon from the Data Tools group.
Final Result >
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]
Alternative Way #2: Use Formula to Remove the First Word in Excel
Step_1: Write this formula in cell B2.
=RIGHT(A2, LEN(A2) - FIND(" ",A2))
Step_2: Press the ENTER key to apply the formula.
- 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.
Step_3: Next, drag the Fill Handle icon to copy the formula in the cell range B2:B10.
Final Result >
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
Alternative Way #3: Use Find and Replace to Remove the First Word in Excel
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.
Step_1: First, select the targeted cell range A2:A10.
Step_2: Then press CTRL+H.
This will bring the Find and Replace dialog box onto the screen.
Step_3: Go to the Replace tab.
Step_4: Then type an asterisk and <space> “* ” in the Find what box.
Step_5: Now keep the Replace with box empty.
Step_6: Click on the Replace All button.
A message box will pop up on the screen.
Step_7: Click on the OK button.
Final Result >
The Find and Replace feature has removed the first word from all the selected cells of the dataset. But 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!