Beginner VBA Needs Help - Text Problem

[DanG]

I am trying to get only the "Last Name" to show on a field that has a full name in it.
Example:
Currently:
Mary jane Rogers
Bill Martin

Would like:
Rogers
Martin

I don't really seem to have a good handle on text manipulation in general.
The last name is always at the end in this case so I Know I need to start from the far right (Right()) and find my first space (" ") possibly using Instr().
but that is about as far as I can get, I cant seem to put it all together.

Any help would sure be great.
Thank you

 

[Mile-O]

If the full name is in a field it would make more sense to ensure you split the name out - that's the best practice, as you're finding out, because it is much easier to concatenate a name than it is to break it down into its separate components.

There are a few ways to get what you want:

Mid$([FullName], InStr(1, [FullName], " ") + 1)

This uses two string functions. Mid$() and InStr(). The first is InStr(). Let's look at it on its own:

InStr(1, [Fullname], " ")

Okay, let's say our FullName field contains John Smith. Let's replace this in the code:

InStr(1, "John Smith", " ")

Okay, the three sections of InStr are the starting point, the text to search in, and the text to search for. So, we are starting from the first character on the left, and searching "John Smith" for a " ".

As it happens there is a " " in "John Smith". As we are starting from the first character the InStr function returns the number 5 because " " is the fifth character in "John Smith".

In the meantime, I'll refer to this value of 5 as X.

Next up is the Mid$() function.

Here, we have:

Mid$([FullName], InStr(1, [FullName], " ") + 1)

We, know the values of [FullName] and the InStr() function so let's replace them with "John Smith" and X's value of 5.

Mid$("John Smith", 5)

So, now we are looking to return the content of the string from the fifth character onewards. The fifth characer is " ", as we've discovered so that means we will get the following value: " Smith". We don't want to return the " " within our surname so we add 1 to the value of the InStr() function as noted in my original expression. This is to offset the " ".

The only problem is when names contain more than a forename and a surname.

That's where the Right$() and InStrRev() functions come in:

Right$([FullName], InStrRev([FullName], " "))

I'm sure you can work out how it works now.

Also:

You can copy the small function below into a standalone module.

Public Function GetSurname(ByVal strText As String) As String

    Dim strParts() As String
    
    strParts = Split(strText, " ")
    
    GetSurname = strParts(UBound(strParts))

End Function

Then, to get the surname, we use the following line:

[i]mySurname[/i] = GetSurname([FullName])

 

[DanG]

I can not tell you how much I appreciate the lesson.
I am at the "For Dummies" stage of all this VBA stuff and it is help like
this that puts it all together for me.

Thank you very much.

 

[DanG]

I'm getting there...
The function you gave me doesn't work becaust I have A97 which does not have some of the sub functions you used.
I have however been playing using some of the concepts you taught me and have learned a ton.
Though I still am unable to complete my mission and bow my head in shame

 

[Mile-O]

Now, there are ways to do this - as usual. You could emulate the Split() function in VBA but, I don't see the point.

It would be much simpler just to reverse the string, use Left$() to get up to the first space, and then reverse the result.

So, here's a quick reverse function:

Public Function Rev(ByVal strText As String) As String

    Dim lngCount As Long
    
    For lngCount = 1 To Len(strText) ' 1st character to last character
        ' reverse one character at a time
        Rev = Mid$(strText, lngCount, 1) & Reverse
    Next lngCount ' Return loop counter
    
End Function ' Rev

And, here's how to use it in an expression:

Rev(Left$(Rev([FullName]), InStr(1, Rev([FullName]), " ") - 1))

Let's just look at how it works bit by bit.

First, the InStr() bit again.

InStr(1, Rev([FullName]), " ")

Replace [FullName] with "John Smith"

InStr(1, Rev("John Smith"), " ")

OKay, let's "break the brackets of the Rev() function by just replacing it with the expected output:

InStr(1, "htimS nhoJ", " ")

From the earlier post you should understand that the return of this function, simply looking for the space will return 6, since the space is the sixth character. Again, we offset this by one - this time reducing the value by 1.

InStr(1, "htimS nhoJ", " ") - 1

OR:

6 - 1

Again, that's 5, so let's replace it with X.

Rev(Left$(Rev([FullName]), [b]X[/b]))

Next up is that Left$() function.

Left$(Rev([FullName]), [b]X[/b])

Let's replace [Fullname] with "John Smith"

Left$(Rev("John Smith"), [b]X[/b])

And then break the brackets of the Rev() function:

Left$("htimS nhoJ", [b]X[/b])

And replace X with its actual value:

Left$("htimS nhoJ", 5)

So, the Left$() function takes the first five characters of the string "htimS nhoJ" which just happen to be htimS.

Let's put that into what we have left of our formula

Rev("htimS")

Since this reverses the string we are left with Smith.

So, the expression you want is:

Rev(Left$(Rev([FullName]), InStr(1, Rev([FullName]), " ") - 1))

Each line shows it broken down bit by bit:

Rev(Left$(Rev([FullName]), InStr(1, Rev([FullName]), " ") - 1))
Rev(Left$(Rev("John Smith"), InStr(1, Rev("John Smith"), " ") - 1))
Rev(Left$("htimS mhoJ", InStr(1, "htimS nhoJ", " ") - 1))
Rev(Left$("htimS mhoJ", 6 - 1))
Rev(Left$("htimS mhoJ", 5))
Rev("htimS")
[b]Smith[/b]

 

[DanG]

Thank you again for all your help.
You really layed this out well, not that I understand it all yet, but it won't be long before I do.

 

[DanG]

Ok, this should be the last question and then I can quit beating this dead horse...
In your "Rev" function, what does the " & Rev" part do?
Rev = Mid$(strText, lngCount, 1) & Rev
I understand everything else but that.

Thank you once again!

 

[DALeffler]

This problem can be simplified if the string is searched for spaces from right to left to begin with, as in:

Public Function fncGetLstName(ByVal strName As String) As String
    Dim I As Integer
        
    fncGetLstName = strName     'return strName on no spaces in string

    For I = Len(strName) To 1 Step -1               'loop from string right to string left
        If Mid(strName, I, 1) = " " Then            'space found?
            fncGetLstName = Mid(strName, I + 1)     'yes - get all characters right of space
            Exit For                                'done
        End If
    Next I                                          'check next character
    
End Function

 

[Mile-O]

In your "Rev" function, what does the " & Rev" part do?
Rev = Mid$(strText, lngCount, 1) & Rev
I understand everything else but that.

As the loop progresses, the value of Rev builds up a string by adding each character of the "John Smith"
i.e.

The loop will run ten times since the length of "John Smith" is ten characters.

1. Rev = "", Mid$("John Smith", 1, 1) = "J"; therefore Rev = "J" & "" so "J"
2. Rev = "J", Mid$("John Smith", 2, 1) = "o"; therefore Rev = "o" & "J" so "oJ"
3. Rev = "Jo", Mid$("John Smith", 3, 1) = "h"; therefore Rev = "h" & "oJ" so "hoJ"

You get the idea...


If you put checkpoints on the code, the dots in the grey bar to the left of the code, then you can follow each line of the code, pass the cursor over the variables, etc. to see their value in each iteration of the loop.

 

[DanG]

Again...Thank you.