How to Extract Text After Space Character in Excel?

Data cleaning is probably the most common data pre-processing task.

This becomes especially necessary when the data is imported from an external source, which was probably formatted differently.

One of the most annoying problems with imported data is the presence of unnecessary spaces.

Although they seem harmless, they could potentially come in the way of subsequent data processing, leading to unexpected results.

In this tutorial we will show you 2 ways to extract text after the space character in Excel:

using a Formula
using VBA

We will also show you how to use a quick Excel tool to extract text after every space character in a given cell.

Table of Contents

Extracting Text after the Space Character in Excel

Below I have a dataset where I have the name and the ID in the same cell (separated by space characters)

We want to extract only the name after the employee id.

In other words, we want to extract the text that comes after the first space from each cell.

There are two ways to get this done.

So let’s get started!

Method 1: Using a Formula to Extract Text after Space Character in Excel

The first method is to use a formula that consists of the RIGHT, LEN, and FIND functions.

Let us first see what each of these functions do:

The RIGHT Function

The RIGHT function extracts a given number of characters starting from the right side of the text.

Below is the syntax of the RIGHT function:

=RIGHT(text, num_of_characters)

In the above syntax:

text is the string from which you want to extract characters
num_of_characters is the number of characters that you want to extract from the text string, starting from the right.

For example, to extract the last 3 characters from the string “Hello” you would use the function as follows:

=RIGHT(“Hello”,3)

Result: ”llo”

The FIND Function

The FIND function finds the index or position of a given character or substring inside a given text. It’s syntax is:

=FIND(search_string,text)

In the above function:

text is the text string that you want to search for. This can be a cell reference or a string.
search_string is the character or substring for which you want to find the position in text.

For example, to find the position of the substring “el” in the text “Hello”, you would use the function as follows:

=FIND(“el”,”Hello”)

Result: 2

We get result 2, because the substring “el” starts from the second position of the string “Hello”.

The LEN Function

The LEN function simply computes the number of characters in a given text. It’s syntax is:

=LEN(text)

Here, the text is the text for which we want to know the number of characters in it.

Putting Together the LEN, RIGHT and FIND Functions to Extract Text after Space

To remove text after the first space character, you need to combine the above three functions into the following formula:

=RIGHT(text,LEN(text)-FIND(" ",text))

Let’s apply this to our example and see what we get as the result:

RIGHT formula to get name after the space character

How did this Formula Work?

To understand how this formula worked, we need to break it down:

First, we used the FIND function to find the position of the space character in the text: FIND(“ “,A2).
We want to count the number of characters after (and not including) the space character. So we subtracted the position of the first space character found from the total length of the text: LEN(A2)- FIND(“ ”, A2).
Finally, we used the RIGHT function to extract the LEN(A2)- FIND(“ ”, A2) characters that follow the space character: RIGHT(A2,LEN(A2)-FIND(” “,A2))

Thus we get all the characters in A2 that come after the first space character.

In case your data has more than one consecutive space, you can tackle them altogether by wrapping a TRIM function around the above formula, as follows:

=TRIM(RIGHT(text,LEN(text)-FIND(" ",text)))

TRIM formula to remove any additional spaces

Also read: How to Extract First Name in Excel

Method 2: Using VBA to Extract Text after Space Character in Excel

Another way to quickly extract text after space character is by using a simple VBA code.

Here’s the code we will be using. You can select and copy it:

Sub extract_text_after_space()
Dim rng As Range
Dim cell As Range
Set rng = Application.Selection
For Each cell In rng
cell.Offset(0, 1).Value = Right(cell, Len(cell) – InStr(cell, ” “))
Next cell
End Sub

Follow these steps:

Click the Developer tab in the ribbon.
Click the Visual Basic option. This will open the VB Editor window
Click on the Insert option in the menu and then click on Module. This will add a new module where you can insert the VBA code
Copy-paste the above lines of code and paste it into the module code window.

Now that you have the VBA code in the file, below are the steps you can use to run the code:

Select the range of cells that contain the text where you want to extract the characters after the space character. You need to make sure adjacent column is blank, because this is where the macro will display the results.
Click the Developer tab
Click on Macros
In the Macros dialog box that opens up, select extract_text_after_space
Click on Run

You will now see the extracted text next to your selected range of cells.

Note: The above code displays the extracted text in the immediate next column, so make sure the column to the right of your original text is blank. Also, remember that the changes made by the VB code are irreversible, so make sure you have a backup copy of the data.

Output where data after space character has been extracted

Also read: Find the Position of a Character in a String in Excel

Extracting Text after Every Space Character in Excel

Now let us take a look at a case where you might want to extract the text after every space character in a given cell, instead of just the first space character.

For example, say you have the string “IKS103 John Smith”.

You might want to extract each word that follows a space in a separate column, as shown below:

Splitting all text after space characters

For this, here’s a really easy and quick trick.

Excel provides a handy tool called the ‘Text to Columns’ feature.

This feature lets you separate text into different columns based on a given delimiter. For example, in our sample data set, each word is delimited by a space.

To extract text after every space using the Text to Columns feature, here are the steps that you need to follow:

Select the column that has the space characters after which you want to split the text.
Click Data tab and then select the “Text to Columns” options (it’s under the Data Tools group).

This will open the ‘Convert Text to Columns’ dialog box.

In Step 1, make sure the radio button next to the ‘Delimited’ option is checked.

Select Delimited in Step 1 of Text to Columns

Click Next.
In Step 2, make sure that only the checkbox next to the Space option is checked.

There is also a checkbox that lets you specify if you want to ‘Treat consecutive delimiters as one’. This means if you have more than one consecutive space character, you want Excel to trim them all into one. Leave this option checked if you need to.

Select Treat consecutive delimiters as one

In the Data preview section, you will be able to see how the final output would look like

Click Next.
In Step 3, there is an option to specify the format of the extracted data. By default, the General option would be selected – this ensures that the output columns have the same format as the original cells. You can leave the default General option selected.

In the ‘Destination’ input field, enter the reference to the cell where you want the first separated string to start. We want to start from cell B2, so enter $B$2 in the input field.

Click Finish.

You can now see that each cell’s text has been separated into different columns:

Final output where text after space character is split in cells

If you don’t want to keep the first column (ID), then you can go ahead and delete the whole column by selecting its column heading, right-clicking, and selecting “Delete”.

Here’s the final look:

In this tutorial, we showed you two ways to extract text after the space character in Excel.

The first method involves the use of a formula, while the second method involves some VBA scripting.

We also showed you how you can extract text after every space in a given cell, by using Excel’s Text to Columns feature.

We hope you found this tutorial easy and helpful.

Other Excel tutorials you may also find useful: