Extracting text - is there a simpler, more elegant way to solve this problem?

[blueguitar23]

Hi all

I want to extract the word Gas from the text string "Walking Gazelle Gas-Water" - text in cell A15. I have a solution, which is:


=MID(A15,FIND(" ",A15,FIND(" ",A15)+1)+1,LEN(A15)-FIND(" ",A15,FIND(" ",A15)+1)-(LEN(A15)-LEN(RIGHT(A15,FIND("-",A15)))+1))


But I did wonder if anyone knew of a shorter, simpler way of solving the problem.


Thanks for reading


Stan

 

[koi]

if your word to find always has "-" and always consist of 3 words, you can try below


=MID(A15,FIND("-",A15,1)-4,4)

 

[Marc L]

Hi !


I prefer a two cells way to avoid gas-factory formulas …     For example :


cell G15 formula :   =REPLACE(A15,1,FIND(" ",A15,FIND(" ",A15)+1),"")


cell H15 formula :   =MID(G15,1,FIND("-",G15)-1)


 

For a unique cell formula in a shorter way,

nothing else than a VBA personal function like this one (to be inserted in a normal module) :

[pre]

Function ExtractLast(After As String, Before As String, Cel As String) As String
P = InStrRev(Cel, After)
If P Then E = InStr(P + 1, Cel, Before)
If E Then ExtractLast = Mid(Cel, P + 1, E - P - 1)
End Function

[/pre]

The cell formula is :   =ExtractLast(" ","-",A15)

 

 

[oldchippy]

Another alternative


=IFERROR(MID(A15,FIND("Gas",A15),3),"Not found")

 

[Marc L]

By the way, nobody thinks to the shortest formula :   Gas   ‼

 

[blueguitar23]

Thanks for all your suggestions. I'll post back when I've tried them out.


Stan

 

[SirJB7]

Hi, blueguitar23!


If for extracting you mean removing you can use this:

=SUSTITUIR(A1;"Gas";"") -----> in english: =SUBSTITE(A1,"Gas","")


Regards!