I used a formula to parse a field that typically contains text like the following:
1F5H or 13F1H
The following formula works just fine when the number of digits to the left of a given character is static, like 1 numerical digit. However, I'm not sure how to determine how many numerical digits there are without making my formula way to complicated.
The following formula works perfect but is currently only setup for 1 numerical digit to the left of "H" or "F"
=IF(AND(ISNUMBER(SEARCH("H",T3)),ISNUMBER(SEARCH("F",T3))),MID(T3,FIND("F",T3)-1,1)*201+MID(T3,FIND("H",T3)-1,1)*138+R3,IF(ISNUMBER(SEARCH("H",T3)),MID(T3,FIND("H",T3)-1,1)*138+R3,IF(ISNUMBER(SEARCH("F",T3)),MID(T3,FIND("F",T3)-1,1)*201+R3,IF(U3>0,U3*100+R3,IF(V3>0,V3*50+R3,IF(W3>0,W3+R3,0))))))
There might be a simpler way of doing this formula too. Grateful for any help. Thank you
1F5H or 13F1H
The following formula works just fine when the number of digits to the left of a given character is static, like 1 numerical digit. However, I'm not sure how to determine how many numerical digits there are without making my formula way to complicated.
The following formula works perfect but is currently only setup for 1 numerical digit to the left of "H" or "F"
=IF(AND(ISNUMBER(SEARCH("H",T3)),ISNUMBER(SEARCH("F",T3))),MID(T3,FIND("F",T3)-1,1)*201+MID(T3,FIND("H",T3)-1,1)*138+R3,IF(ISNUMBER(SEARCH("H",T3)),MID(T3,FIND("H",T3)-1,1)*138+R3,IF(ISNUMBER(SEARCH("F",T3)),MID(T3,FIND("F",T3)-1,1)*201+R3,IF(U3>0,U3*100+R3,IF(V3>0,V3*50+R3,IF(W3>0,W3+R3,0))))))
There might be a simpler way of doing this formula too. Grateful for any help. Thank you