extract specific word from a string of words

[Rhon Alvarez]

Can somebody help me figure out this one please. I need to extract all words starting with AUS5, i.e. AUS5ROSANA, AUS5ENT, AUS5EALTEZ, AUS5MON, AUS5ARMI. my problem is that I don't have specific number of characters after the word AUS5 and even the positioning of the words vary on each entries. at the end of the day, I just need to extract the AUS5*** words in order to sort it out and add all the other details incorporated therein. I attached a sample file.
Please help me....

 

[Asheesh]

Hi Rhon -

Try the below in cell B2 and drag it down..

IFERROR(TRIM(MID(A2,SEARCH("aus5",A2),SEARCH(" ",A2,SEARCH("aus5",A2))-SEARCH("aus5",A2))),"")

 

[Asheesh]

Edit -

Just noticed an issue with above formula -

Try this one:

IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH("aus5",A2),SEARCH(" ",A2,SEARCH("aus5",A2))-SEARCH("aus5",A2)),"-",REPT(" ",255)),255)),"")

 

[shrivallabha]

One more would be:
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1;SEARCH("AUS5";A1&"AUS5";1);99)&" ";" ";REPT(" ";99));"-";REPT(" ";99));99))

Note: Replace ; with , depending on your locale settings.