How to extract the last word in a string?

[roblee]

I've imported a CSV file into my Access Database and the Address field also contains a City name, ie:
123 SE Main St. #A Seattle
8235 127th St Ct E Tacoma
47 Wilson River Highway Unit H1 Bremerton

I want to parse the City into a new field and then delete the city name from the address field. I can use this expression in a query to parse to the firstspace:

Expr: Left([mailaddr1],InStr([mailaddr1]," "))

but is there a function which will easily allow me to extract the last word in a string?

[TheSmileyCoder]

You can use Right and InStrRev.
TheSmileyCoder

[ADezii]

Here is a Custom Function that I created for you, which will do what you request. The Function Definition, Sample Calls, as well as Output are listed below:

Public Function fExtractLastWord(strWord As String) As Variant
Dim varRet As Variant
 
varRet = Split(strWord, " ")
 
fExtractLastWord = varRet(UBound(varRet))
End Function

Debug.Print fExtractLastWord("8235 127th St Ct E Tacoma")
Tacoma

Debug.Print fExtractLastWord("47 Wilson River Highway Unit H1 Bremerton")
Bremerton

Debug.print fExtractLastWord("Help")
Help

P.S. - Here is another alternative, but you must have at least 1 Space in the String or an Error will occur:

Debug.Print Mid$("<String Here>", InstrRev("<String Here>"," "))

[OldBirdman]

That will not work for Walla Walla, Port Angeles, or Saint Helens. Even the example "8235 127th St Ct E Tacoma" could be mis-parsed if E Tacoma were a city.

[ADezii]

@OldBirdman - There will definitely be Records that fall through the crack, but there is no method to differentiate which Strings comprise various Cities, unless of course, you incorporate some kind of Lookup Table into the Logic. A Table consisting of all City Names that consist of 2 or more syllables would do the trick.

[OldBirdman]

@ADezii - I'm not critizing your methods. I was pointing out to original poster that this, or any, function cannot determine the city name from its position in the string only.

[ADezii]

@OldBirdman - You are absolutely correct in your statements. This is obviously something that I should have indicated to the OP along with the Code. Thanks for catching my oversight.