Extract last 2 words of a string

[Duxster]

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]

Hi Rob. Maybe you could try something like this:

Mid([StringField], InStrRev([StringField]," "), InStrRev([StringField]," ")-1)+1)

Hope that helps...

 

[pbaldy]

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]

Geez, again too slow.

 

[Duxster]

Hi Rob. Maybe you could try something like this:

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]

as, suggested you can make a function using split():

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;

 

[Duxster]

It worked perfectly, thank you so much.

 

[theDBguy]

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.

Mid([StringField], InStrRev([StringField]," ", InStrRev([StringField]," ")-1)+1)

 

[Duxster]

Great thanks.

 

[theDBguy]

Great thanks.

Sorry again. Good luck with your project.

 

[arnelgp]

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]

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.