Find last space in string

Question

I want to find the last space in a string. The purpose of this is to split a name into it's three parts. I can get the first name easy enough, but the last two are not so simple.

fullName = "Giger,San Paulo Fisher"
first = Left(fullName, InStr(1, ",", fullName, vbTextCompare))
last = Mid(fullName,InStr(1, ",", fullName, vbTextCompare),[POSITION OF LAST SPACE]-InStr(1, ",", fullName, vbTextCompare))
middle = Right(fullName,Len(fullName)-[POSITION OF LAST SPACE])

Thanks in advance!

Answer 1

You can use the Split function without VBA. This will get you "Giger,San","Paulo","and "Fisher". It should be relatively easy to split out the last name and first name by looking for the comma. And you don't have to worry about how many names are included or how long they are.

Dim tStr As String
tStr = "Giger,San Paulo Fisher"
Dim tArray() As String
tArray = Split(tStr, " ")
For i = 0 To UBound(tArray)
    MsgBox tArray(i)
Next I

If you want just the index of the last space. Either of these will do. Note that the second one is using the code above to find the array of strings from the Split function.

MsgBox InStrRev(tStr, " ")
MsgBox InStr(1, tStr, tArray(UBound(tArray))) - 1

Answer 2

There is a fairly well known trick for this (see for example Excel: last character/string match in a string - I adapted my answer from the last version of the one accepted there): you replace one space with lots of spaces, then take the last N characters and trim them:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

You can make the number (99 in this case) as big as you need - longer than the longest word you envisage.

Here is how it works. Imagine your original string (name):

Giger,San Paulo Fisher

Now replace every space with 8 spaces (keeping it manageable - formula uses 99, same principle):

Giger,San        Paulo        Fisher

Now take the last 8 characters:

__Fisher

(I am using underline to show the space)

Now trim it (remove leading and trailing spaces):

Fisher

which is the result you were after.

You can use other strings as well - but since you want to split on a space, it is the most natural string to use.

If you want to do this in VBA, you can just use

lastSpace = InStrRev(fullName, " ")
lastName = Mid(fullName, lastSpace + 1)

By the way - beware of assuming that the thing after the last space is the whole last name. There are many names (more in some countries than others) that consist of multiple words separated by spaces.

Answer 3

its just you can use split command for to separate the name using space

For example: xyz = Split(tStr, " ")