formula form array words to remove whole word that have numbers
Excel Formula:
=IFERROR(IF(FIND({0,1,2,3,4,5,6,7,8,9;0,1,2,3,4,5,6,7,8,9;0,1,2,3,4,5,6,7,8,9},MID(A2,FIND("|",SUBSTITUTE(" "&A2&" "," ","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))),FIND(" ",A2&" ",FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))-FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))))),1)<>0,MID(A2,FIND("|",SUBSTITUTE(" "&A2&" "," ","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))),FIND(" ",A2&" ",FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))-FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))))),""),"")
Excel Formula:
=MID(A2,FIND("|",SUBSTITUTE(" "&A2&" "," ","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))),FIND(" ",A2&" ",FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))-FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))
HHH.xlsx | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Text | Formula | Result | ||
2 | P1 FMD 15h | FMD | |||
3 | P1 | X | |||
NN |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2 | B2 | =IFERROR(IF(FIND({0,1,2,3,4,5,6,7,8,9;0,1,2,3,4,5,6,7,8,9;0,1,2,3,4,5,6,7,8,9},MID(A2,FIND("|",SUBSTITUTE(" "&A2&" "," ","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))),FIND(" ",A2&" ",FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))-FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))))),1)<>0,MID(A2,FIND("|",SUBSTITUTE(" "&A2&" "," ","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))),FIND(" ",A2&" ",FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))-FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))))),""),"") |
B3 | B3 | =MID(A2,FIND("|",SUBSTITUTE(" "&A2&" "," ","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))),FIND(" ",A2&" ",FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))-FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))))) |
Press CTRL+SHIFT+ENTER to enter array formulas. |