Extract last 2 words of a string (1 Viewer)

Duxster

New member
Local time
Today, 06:54
Joined
Sep 17, 2021
Messages
5
Hi

I'm stuggling to find a way within a query to extract the last two words in a string, the string can be any length. I can get the last word but not the last two. Any advice would be appreciated.

Thanks

Rob
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:54
Joined
Oct 29, 2018
Messages
21,358
Hi Rob. Maybe you could try something like this:

Code:
Mid([StringField], InStrRev([StringField]," "), InStrRev([StringField]," ")-1)+1)
Hope that helps...
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:54
Joined
Aug 30, 2003
Messages
36,118
You can create a function that accepts the string as input. Use the Split() function on the string and get the last 2 with UBound and UBound - 1.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:54
Joined
Aug 30, 2003
Messages
36,118
Geez, again too slow.
 

Duxster

New member
Local time
Today, 06:54
Joined
Sep 17, 2021
Messages
5
Hi Rob. Maybe you could try something like this:

Code:
Mid([StringField], InStrRev([StringField]," "), InStrRev([StringField]," ")-1)+1)
Hope that helps...
Thanks for this. I have tried it but it's saying too many closing parentheses. Any ideas?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:54
Joined
May 7, 2009
Messages
19,169
as, suggested you can make a function using split():
Code:
Public Function fnLast2Words(ByVal sp As String) As String
Dim var
sp = sp & ""
If Len(sp) Then
    Do Until InStr(1, sp, "  ") = 0
        sp = Replace$(sp, "  ", " ")
    Loop
    var = Split(sp, " ")
    If UBound(var) > 0 Then
        fnLast2Words = Trim$(var(UBound(var) - 1)) & " " & Trim$(var(UBound(var)))
    End If
End If
End Function

select *, fnLast2Words([fieldName]) As Last2 from yourtable;
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:54
Joined
Oct 29, 2018
Messages
21,358
Thanks for this. I have tried it but it's saying too many closing parentheses. Any ideas?
Hi. Sorry about that. I guess I meant it to be this way.
Code:
Mid([StringField], InStrRev([StringField]," ", InStrRev([StringField]," ")-1)+1)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:54
Joined
May 7, 2009
Messages
19,169
Problem with Instr() approach, it is not robust. You can have a null or a single word in a field and it will #Error out.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:54
Joined
Jan 20, 2009
Messages
12,849
Problem with Instr() approach, it is not robust. You can have a null or a single word in a field and it will #Error out.
Concatenating the search character onto the end (or beginning in the case of InStrRev) of the searched string avoids both these problems.

The advantage of using engine functions over user defined functions is performance while the advantage of the UDF is the ability to include refinements like dealing with double spaces as Arnelgp has demonstrated.

BTW A Null would error out his function too because its input parameter is String. It could be substituted with Variant but it would be less efficient.
 

Users who are viewing this thread

Top Bottom