Extract data from string with variablle length

[Palmcrest]

Hi I have some data I would like to extract two values from.
The string length can be variable so a fixed count will not work.
I want to extract the second last bit of data in this example I want to extract
SMI from "56W Mech Insp BMM SMI STK7"

in the the scond example I want to extract
SBGSM from "56W Mech Svce BMM SBGSM BWR10"

Since the strings are of different lengths I am struggeling to just use the mid/find function on its own.

Hope that makes sense , help appreciated

 

[SkipVought]

HI,

There's a complex spreadsheet formula that might help

http://www.exceltactics.com/reverse...ing-find/2/#The-Reverse-String-Search-Formula

Personally, I'd use a VBA function that's pretty easy to code ...

Function WordFromEnd(sString As String, nThFromEnd as Integer)
   Dim a

   a = Split(sString, " ")

   WordFromEnd = a(UBound(a) - nThFromEnd + 1)
End Function

Paste this code into a MODULE (alt+F11 to toggle between VBA editor and sheet) and use like any other spreadsheet function. Supply the reference to the cell containing the string an pd the number of the word of interest, from the right: which in this case is 2.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

I would tackle this by writing a user-defined function in VBA.[ ] Inside that UDF I would probably use VBA's "Split" function.

Incidentally, you say you want to extract TWO substrings but your examples deal only with one substring (the second last one).

 

[Deniall]

Skip and I must have been typing simultaneously as well as thinking similarly.

 

[Palmcrest]

Thank you both for your very good feedback.

I would however prefer not to use VBA in the instance. (Document management reasons)

To get STK7 from "56W Mech Insp BMM SMI STK7" I used
=TRIM(RIGHT(SUBSTITUTE(K2," ",REPT(" ",LEN(K2))),LEN(K2)))

Now that I have STK7 I think I can use the "len" function to show the starting position of the data I want.

I thought using MID with the starting point in the string using the len of STK7 minus the Len of the whole string.

Im just not sure how to put this together.

So I have a string
"56W Mech Insp BMM SMI STK7"
I can show the end of the string in another colum "STK7"
I think I should be able to use that info to grab "SMI" from the string.

What do you think?

 

[SkipVought]

So with that string, length 4 plus 1 space return the left of the whole sting, LEN() of the whole string minus (4+1), and find the last word in THAT string.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Palmcrest]

Thanks again guys
For the second last "word" in the string I found this

=TRIM(LEFT(RIGHT(SUBSTITUTE(H4," ",REPT(" ",LEN(H4))),2*LEN(H4)), LEN(H4)))

Changing the 2 targets the word in the string.

Cheers