Count Number of digits 0-9 to the left of a Specific Character in a Cell

[MacIndy]

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

 

[Peter_SSs]

if your data is always number,letter,number,letter, etc. you should never have a problem.

What about 1E5H?

 

[Eric W]

Good point, I overlooked that case. I'll leave it to the OP if that's a possibility, and if so, look for a more robust option.

 

[Peter_SSs]

Good point, I overlooked that case.

Assuming the data is number-letter-number etc as you had already mentioned (& all upper case per the samples), then I think "E" is the only problem, so perhaps

=AGGREGATE(14,6,MID(SUBSTITUTE(T3,"E","X")&"0h",SEARCH("h",T3&"0h")-{3,2,1},{3,2,1})*138,1)