Puzzling - Extract the word with PH- in it, start, middle or end of cell location

Hi guys,

I've been working on this for hours, searching google and threads but I just can't get it to work.
I need to extract the text in a cell, but not everyone puts it in the same place. Not only that, but the PH- value length is variable.

This works if it's the first word in the cell, and a space following it, but 10% of my list is not so simple:
=MID(B2,FIND("PH-",B2)-(FIND("PH-",B2)-1),FIND(" ",B2)-1)

And this formula is working great in my tests, except that I can't figure out how to count the location of the word with PH- in it, so it only works if I manually do it (see attached file for example) - any help would be appreciated!

=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),(C2-1)*LEN(B2)+1, LEN(B2)))

I'm not sure if the attachment is working, so here is the formula:
Result Source word location formula
52PH-1233 52PH-1233 1 TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),(C2-1)*LEN(B2)+1, LEN(B2)))
52PH-12345 Crazy but 52PH-12345 3 TRIM(MID(SUBSTITUTE(B3," ",REPT(" ",LEN(B3))),(C3-1)*LEN(B3)+1, LEN(B3)))
52PH-1235 52PH-1235 and 1 TRIM(MID(SUBSTITUTE(B4," ",REPT(" ",LEN(B4))), (C4-1)*LEN(B4)+1, LEN(B4)))
522PH-1233 120 522PH-1233 then 2 TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (C5-1)*LEN(B5)+1, LEN(B5)))

Originally Posted by FDibbins

Whats wrong with the formula you have there?
can you show some samples where it doesnt work?

Hi there, the formula works if i manually put the word location in the c column. But i download a new file every day with over 200 lines. If i can figure out a way to automatically determine which word in the cell has the "PH-" value in it, that's perfect.

Thanks so much guys for such quick help! I love a puzzle just as much as the next guy, but at some point the banging of head against the keyboard stops being fun.

Fdibbins - your solution was almost there, but wasn't working on the sequences with more than 2 prefix before the PH-

FlameRetired - BEST SOLUTION - I tried this formula on my data sequence and it found the results almost every time, even when there were asterisk in front of the PH-, the only time it didn't work was if they didn't have any prefix included in front of the PH- and that was hardly any. So thank you soo much sir!!

nflsales - THANKS for your suggestions as well. Your's worked 8-10 times, but I still would have to do a bit of cleanup, and it did pick up those couple that Dave's missed. A good second option to have available.

I'll save these to my OneNote excel cheat sheet as I'm sure I'll be needing them again. Thanks again EVERYONE. Really appreciate the help!!