Sometimes, you may need to extract a particular text to get better visualization and comparison. For example, you want to extract only the customers’ names without the Order ID placed in the same cell. In this tutorial, we will show you how to extract text after the first space in Excel.
How to Extract Text After First Space in Excel: 3 Suitable Ways
In the following sections, we will demonstrate 3 suitable ways comprised of several functions such as the MID, the FIND, the LEN, and the RIGHT functions. Additionally, we will apply a VBA code to do the same extraction of text. In the below image, a sample data set is provided to accomplish the task.
1. Merge MID Function with FIND Function to Extract Text After First Space in Excel
First of all, we will apply The MID Function and The FIND Function to extract text as we need. Firstly, know how the FIND function works.
Syntax of the FIND Function:
=FIND(find_text, within_text, [start_num])
Explanation of the Arguments:
- Find_text is the specific character we want to find in a reference cell.
- Within_text is the reference cell where the text character is located.
- Start_num is the serial number of the specific character. If we enter 1 it will return the first specific character number and if we enter 2, it will return the second specific character.
Return Value:
The FIND function returns the character number of a specific character in a text.
Step 1: Select the find_text Argument
- As we want to find the space, we type (“ ”) as our find_text
=FIND(" ",)
Step 2: Select the within_text Argument
- Click on cell B5 to select it as our within_text
=FIND(" ",B5)
Step 3: Type the start_num Argument
- As we want to find the first space in cell B5, type 1 as the start_num argument with the following formula.
=FIND(" ",B5,1)
Step 4: Find the Space Character Number
- Press Enter to see the character number of space. As the first space is in the 8 number character in cell B5, it will result in 8.
Step 5: Apply the MID Function
Syntax of the MID Function:
=MID(text, start_num, num_chars)
Explanation of the Arguments:
- Text is the reference cell where the text character is located.
- Start_num is the first character number from which it will return the value.
- Num_chars is the last character number. It will return the result up to that character in the character number serial.
Return Value:
The MID function returns the cell value from Start_num to Num_chars character serial number.
- For the text argument, select cell B5 with the following formula.
=MID(B5,FIND(" ",B5,1)
- For the start_num argument, enter the value returned from the FIND function from Step 4.
=MID(B5,FIND(" ",B5,1)
- Enter the num_chars argument as 20 with the following formula.
=MID(B5,FIND(" ",B5,1),20)
Step 6: Results
- Finally, press Enter to see the result with the extracted value.
- Use the AutoFill tool to apply the same formula in the required blank cells.
Read More: How to Extract Text Before Character in Excel
2. Combine RIGHT, LEN, and FIND Functions to Extract Text After First Space in Excel
In the following sections, we will combine the 3 functions of the RIGHT, the LEN, and the FIND function to extract text after space. Follow the steps below to complete the task.
Step 1: Apply the FIND Function
- Like previously, enter the FIND function with the following formula. It will return 8 as the character number of the space.
=FIND(" ",B5,1)
Step 2: Insert the LEN Function
Syntax of the MID Function:
=LEN(text)
Explanation of the Arguments:
- Text is the reference text value.
Return Value:
The LEN function returns the character number of a specific text.
- To find the difference in character numbers from the right side, type the following formula.
=LEN(B5)-FIND(" ",B5,1)
- Therefore, it will return 13 as a result.
Step 3: Insert the RIGHT Function
Syntax of the RIGHT Function:
=RIGHT(text,[num_chars])
Explanation of the Arguments:
- text is the reference text value.
- Num_chars is the number of a specific character from the right side.
Return Value:
The RIGHT function returns the text for a specific character number from the right side.
- To select B5 as the text argument, type the following formula.
=RIGHT(B5,LEN(B5)-FIND(" ",B5,1))
- To select the num_chars argument, insert the return value of the LEN function from Step 2 with the following formula.
=RIGHT(B5,LEN(B5)-FIND(" ",B5,1))
Step 4: Results
- Finally, press Enter to see the first extracted value after space.
- Therefore, use the AutoFill tool to get all the extracted values.
Read More: How to Extract Text after a Specific Text in Excel
3. Run a VBA Code to Extract Text After First Space in Excel
In addition to the previous sections, you can accomplish the same thing with the help of the Excel VBA. Follow the instructions below to do the task.
Step 1: Create a Module
- Firstly, press Alt + F11 to open the VBA Macro.
- Click on the Insert.
- Then, select the Module.
Step 2: Write a VBA Code
- Paste the following VBA
Sub VBA_to_extract_text()
'Declare variable
Dim Selection_Rng As Range
Dim cell_Value As Range
'Define Selection range to get from selection
Set Selection_Rng = Application.Selection
'Apply for loop
For Each cell_Value In Selection_Rng
'Formula to extract text after space
cell_Value.Offset(0, 1).Value = Right(cell_Value, Len(cell_Value) - InStr(cell_Value, " "))
Next cell_Value
End Sub
Step 3: Select the Cells
- Select the cells in the range.
Step 4: Run the Program
- Firstly, click on the Save
- Then, click on the Play icon or press F5.
Step 5: Final Results
- After running the program, all the values will be extracted at once as shown in the image below.
Similar Readings
- How to Extract Text after Second Space in Excel
- How to Extract Text After Last Space in Excel
- How to Extract Text Between Two Commas in Excel
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Finally, I hope you now understand how to extract text after the first space in Excel. All of these strategies should be carried out when your data is being educated and practiced. Examine the practice book and apply what you’ve learned. We are driven to continue offering programs like this because of your generous support.
If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below. Stay with us and continue to learn.
