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

#### MacIndy

##### New Member
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### 6StringJazzer

##### Well-known Member
Can you tell us what you want it to do? I am not going to try and reverse engineer your existing formula. You description does not go any further than "parse a field."

#### AhoyNC

##### Well-known Member
Something like this?
This is an array formula and must be entered with CTRL-SHIFT-ENTER

Book1
AB
11F5H2
213F1H3
31H34Y1
412JI5K6Hl64
Sheet1
Cell Formulas
RangeFormula
B1:B4B1{=COUNT(--(MID(A1,ROW(INDIRECT("1:"&SEARCH("H",A1)-1)),1)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

#### lrobbo314

##### Well-known Member
A way using Power Query.

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Count = Table.AddColumn(Source, "Count", each Text.Length(Text.Select(Text.Middle([Code],0,Text.PositionOfAny([Code],{"H","F"})),{"0".."9"})))
in
Count``````

#### jolivanes

##### Well-known Member
VBA Code:
``=FIND("F",A2,FIND("F",A2)) - 1``

#### jolivanes

##### Well-known Member
For any amount of numbers to the left of the first any alphabetical.
VBA Code:
``=MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},UPPER(A1)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))-1``

#### AhoyNC

##### Well-known Member
Here is a small change to my formula in post #3 that will eliminate the need to use CTRL-SHIFT-ENTER.

Book1
AB
11F5H2
213F1H3
31H34Y1
412JI5K6Hl64
Sheet2
Cell Formulas
RangeFormula
B1:B4B1=SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&SEARCH("H",A1)-1)),1)))

#### Eric W

##### MrExcel MVP
This formula:

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

should give you the number in front of an h (times 138) as long as it is 3 digits or less. It returns 0 if there is not an h in the cell. Change the {3,2,1} array constants if you want it to look for more digits. There are some cases where this technique might give bad results, but if your data is always number,letter,number,letter, etc. you should never have a problem. Adapting that formula for the "f" value should be easy. Then you can combine the 2 formulas like this:

=IFERROR(1/(1/(AGGREGATE(14,6,MID(T3&"0h",SEARCH("h",T3&"0h")-{3,2,1},{3,2,1})*138,1)+AGGREGATE(14,6,MID(T3&"0f",SEARCH("f",T3&"0f")-{3,2,1},{3,2,1})*201,1))),IF(U3>0,U3*100,IF(V3>0,V3*50,IF(W3>0,W3,-R3))))+R3

which should give you the same results as your original formula, except that it looks for more digits. There might be room for more improvement, since the U3:W3 range is adjacent. I'll look at that some more.

#### MacIndy

##### New Member
This formula:

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

should give you the number in front of an h (times 138) as long as it is 3 digits or less. It returns 0 if there is not an h in the cell. Change the {3,2,1} array constants if you want it to look for more digits. There are some cases where this technique might give bad results, but if your data is always number,letter,number,letter, etc. you should never have a problem. Adapting that formula for the "f" value should be easy. Then you can combine the 2 formulas like this:

=IFERROR(1/(1/(AGGREGATE(14,6,MID(T3&"0h",SEARCH("h",T3&"0h")-{3,2,1},{3,2,1})*138,1)+AGGREGATE(14,6,MID(T3&"0f",SEARCH("f",T3&"0f")-{3,2,1},{3,2,1})*201,1))),IF(U3>0,U3*100,IF(V3>0,V3*50,IF(W3>0,W3,-R3))))+R3

which should give you the same results as your original formula, except that it looks for more digits. There might be room for more improvement, since the U3:W3 range is adjacent. I'll look at that some more.
This looks like it would work beautifully. Thank you

#### Eric W

##### MrExcel MVP
Glad we could help. Thanks for the feedback!

1,102,154
Messages
5,485,066
Members
407,480
Latest member
breederbulldog

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...