How to Find the Last Space in Text String in Excel?

by

Finding positions of a certain word or character is quite common in Excel.

However, there might be cases where you need to find the position of a certain instance of the character in a string.

For example, you might need to find the position of the last space in a string. Maybe you need it to separate a person’s last name from the rest of the name.

In other cases, you might have the string structured in a given format and you might need to find the position of the last space so that you can remove it.

Whatever the reason, in this tutorial we are going to show you two ways to find the last space in a string in Excel:

  • Using an Excel formula
  • Using a VBA script

Excel Formula to Find the Last Space in a String in Excel

Let’s say we have the following list of strings:

Dataset with text strings

We want to find the positions of the last space character in each string.

Here’s the formula that you can use to find the last space in a string (that is in cell reference A2):

=FIND("/", SUBSTITUTE(A2," ","/", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))

When you press the return key, the above formula returns an integer that tells the position of the last space in the string at cell reference A2.

Copy this formula down to the rest of the cells.

Here’s the result you should get:

Formula to get the last position of space character

Explanation of the Formula

Let’s take some time to understand how this formula worked. We will go over the above formula layer by layer, starting from the innermost one:

  • SUBSTITUTE(A2,” “,””) – This formula removes all spaces from the string in cell A2. It returns the string “TheReaperIntheNorth”
  • LEN(SUBSTITUTE(A2,” “,””)) – This formula finds the total length of the string after removing all the spaces. It returns the value 19.
  • LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)) – This formula subtracts the total length of the string without spaces from the total length of the original string. In other words this function finds out the number of spaces in the string. The formula returns the value 4.
  • SUBSTITUTE(A2,” “,”/”, LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))) – This formula replaces the last space with a random character (‘/’ in this case, but you can use any character you want, making sure that it doesn’t already exist in the original string). The purpose of this is to be able to easily locate it in the string.It returns the string “The Reaper in the/North”.
  • FIND(“/”, SUBSTITUTE(A2,” “,”/”, LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)))) – Now all that the FIND function does is locate and return the position of the ‘/’ character in the string. This is nothing but the position of the last space that we had substituted.

Once you’ve found the position of the last space you can go ahead and perform subsequent actions on the string.

For example, if you want to extract only the text that comes after the last space, you could wrap a RIGHT function around the result of this formula, as shown below:

=RIGHT(A2,LEN(A2)-B2)

The above formula extracts LEN(A2)-B2 characters from the right of the string in cell A2.

RIGHT formula to extract text string after the last character

Alternatively, if you wanted to remove everything after the last space, you could simply substitute everything after the last space with a blank string, as follows:

=SUBSTITUTE(A2,RIGHT(A2,LEN(A2)-B2),"")
removing everything after the last space character

You could further generalize the formula to find the nth-last space in a given string.

For example, to find the second last space, you could subtract 1 from the number of spaces in the string, so that your formula becomes:

=FIND("/", SUBSTITUTE(A2," ","/", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))
Finding the second last space

Similarly, to find the third last space, you could subtract 2 from the number of spaces in the string, so that your formula becomes:

=FIND("/", SUBSTITUTE(A2," ","/", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-2))
Finding the third last space

To generalize, the nth last space in a string can be found by the formula:

=FIND("/", SUBSTITUTE(A2," ","/", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-(n-1)))

There is a possibility that your text string may have leading or trailing spaces. In such cases, instead of using the text string as-is, you can wrap it in the TRIM function. So if the text string is in cell A2, you should use TRIM(A2) in the formulas, This will make sure that leading and trailing spaces are automatically ignored. 

Also read: How to Remove Leading Spaces in Excel?

Using a VBA Custom Function to Find the Last Space in a String in Excel

An alternative way to find the last space in a string is to use a VBA custom function (also known as a User-defined Function).

This is quite handy as it helps simplify your formula, having the bulk of the work done within the function definition.

Moreover, you can save this custom function once and reuse it over and over. So, it’s quite helpful if you have to often perform operations like these.

Let’s say you have the same list of strings and you want to find the position of the last space in each string.

Dataset with text strings

To find the last space, we have created a function called FindLastSpace, and defined it as follows:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Function FindLastSpace(cell As Range)
Dim rng As Range
Dim rLen As Integer
rLen = Len(cell)
For i = rLen To 1 Step -1
If Mid(cell, i – 1, 1) = ” ” Then
FindLastSpace = i – 1
Exit For
End If
Next i
End Function

This function takes in a cell containing text and returns the position of the last space in the form of an integer.

To get this function to work, we need to first put it into the Visual Basic editor.

Here’s how to add this custom function in your Excel so you can use as a regular function in the worksheet:

  1. From the main menu, navigate to Developer->Visual Basic.
Click on Visual Basic
  1. This opens the VBA window, Select Insert->Module and now you can start coding. 
Click on Insert Module
  1. Type in or copy-paste the above script into the module window.
Copy and paste the code in the newly inserted module
  1. Your user-defined function is now ready to be used.

In your Excel worksheet, simply use this function as you would use any other Excel function.

So to find the position of the last space in cell A2, you can enter the following formula in cell B2:

=FindLastSpace(A2)

When you press the return key, you should see the result in cell B2. Copy this formula down to the rest of the cells in column B (using the fill handle).

Here’s the result you should get:

FindLastSpace formula returns the position of the last space character

Explanation of the Script

The main part of the script is in the following few lines:

rLen = Len(cell)
For i = rLen To 1 Step -1
If Mid(cell, i – 1, 1) = ” ” Then
FindLastSpace = i – 1
Exit For
End If
Next i

This block of code starts looping backward, starting from the last character of the given string.

We use the Mid() function to extract a character at each loop.

As soon as the loop encounters a space character, it returns the position (i -1) of the character in the string to the calling function and stops looping.

Your user-defined function (FindLastSpace) should now be available in all the worksheets of your workbook.

If you want it to be available in all your workbooks, then you can either save it in your Personal Macro Workbook or create an add-in from it. 

In this tutorial, we showed you two ways to find the last space in a string.

The first method uses a complex formula, while the second method uses a VBA custom function that you can save in your Personal Macro Workbook and use whenever needed.

We hope you found this helpful.

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment