extracting a string

[ajm]

hi, i am looking to extract a string of text from within a sentence. The string consists of 6 characters or less (4 minimum) with the last three chartacters "HHS" and in 90% of cases, comes after a space. Sometimes, though the string is at the beginning of the sentence. I tried trimming the results of a mid statement using Find to locate the substring (HHS) and then subtracted 3 to get to the start of the string:

(TRIM(MID([@Provider], FIND("HHS",[@Provider])-3,6))

That worked in most cases where the full string is either 6 characters or five in length. It didn't work where the string was at the beginning of the sentence, nor if the length was only 4 characters.

so, how can you extract a string containing "HHS" as the last three characters, where the string is max 6 characters, but could be 5 or 4, that will have a space before it if its at the end of the sentence, but not if its at the start.

examples might be:

Jonnos space ABCHHS
Monkey Gone Bananas - MHHS
Hoots Boots WBHHS Man
XYZHHS Area 54 Open to all ages
Cows Horses Dogs CHHS

 

[Peter_SSs]

Peter, both formulas worked.

Good news.

so, substitute double spaces for single spaces

No, that could possibly lead to an incorrect result. To be sure ..

note that there are double quote marks with larger spaces between them. Each of those larger spaces contain 3 space characters