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)))
Bookmarks