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

 

[Micron]

You showed the inputs but not the expected outputs so maybe I interpreted incorrectly. If all you want is HHS I'm thinking in vba,
- find HHS (Instr function), extract from there using Mid function
- IF Len(str) = 3 (HHS is at the end) you're done, otherwise
- Instr to find space then Left(str)-1

as long as there's always a space between "S" and what follows when HHS is not at the end.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Edit: I realised that I have asked the above before and your response was:

Peter, I work as a contractor so what the employer has is what i get. I will try to remember to pout the version in future questions though.

If you have MS365 or Excel 2021 try

=LET(t,SUBSTITUTE(" "&[@Provider]," ","   "),p,FIND("HHS",t),TRIM(MID(t,p-3,6)))

Otherwise try

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

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

 

[Micron]

Actually, would not need to find the space; just take Left(str,3). Tested:

Function ExtractHHS(strIn As String) As String
Dim strOut As String

strOut = Mid(strIn, InStr(strIn, "HHS"))
If Len(strOut) = 3 Then 
  ExtractHHS = strOut
  Exit Function
End If
  
strOut = Left(strOut, 3)
ExtractHHS = strOut

End Function

 

[alansidman]

an alternative solution with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "HHS", each if Text.Contains([Column1],"HHS") then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([HHS] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"HHS"})
in
    #"Removed Columns"

Book1
	A	B	C
1	Column1		Column1
2	Jonnos space ABCHHS		ABCHHS
3	Monkey Gone Bananas - MHHS		MHHS
4	Hoots Boots WBHHS Man		WBHHS
5	XYZHHS Area 54 Open to all ages		XYZHHS
6	Cows Horses Dogs CHHS		CHHS
Sheet1

 

[ajm]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Edit: I realised that I have asked the above before and your response was:




If you have MS365 or Excel 2021 try

=LET(t,SUBSTITUTE(" "&[@Provider]," ","   "),p,FIND("HHS",t),TRIM(MID(t,p-3,6)))

Otherwise try

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

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

Peter, thank you. I always forget to add it in. Bad form on my part. I'm working government at the moment so will put those details in. will try what you have posted today.

 

[ajm]

You showed the inputs but not the expected outputs so maybe I interpreted incorrectly. If all you want is HHS I'm thinking in vba,
- find HHS (Instr function), extract from there using Mid function
- IF Len(str) = 3 (HHS is at the end) you're done, otherwise
- Instr to find space then Left(str)-1

as long as there's always a space between "S" and what follows when HHS is not at the end.

Thanks Micron. From the top:

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


Desired results:

ABCHHS
MHHS
WBHHS
XYZHHS
CHHS

 

[Micron]

Have to run out for a bit. I wrote a procedure for this on the assumption that all you wanted was HHS. Back to the drawing board, I guess.

 

[ajm]

Peter, both formulas worked. I think i have it for this one:

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

so, substitute double spaces for single spaces making the Provider name longer. Within that name, look for HHS. With the double spaces, subtracting three from the position of the HHS now will find either the start of the six character strings or empty space for any of the short (4 character) HHS strings. Trim then removes the empty spaces.

I have not used Let before. I can see how it will be useful though. especially when doing if statements with vlookup results.

 

[ajm]

Have to run out for a bit. I wrote a procedure for this on the assumption that all you wanted was HHS. Back to the drawing board, I guess.

I appreciate your willingness to help Micron. Thank you. Peter and alan have nailed it so don't feel like you have to provide something.

cheers