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

MacIndy

New Member
Joined
Dec 30, 2019
Messages
5
Office Version
2019
Platform
Windows
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
 

Some videos you may like

Excel Facts

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

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
542
Office Version
2019
Platform
Windows
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
Joined
Oct 10, 2011
Messages
4,535
Office Version
365
Platform
Windows
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
Joined
Jul 14, 2008
Messages
2,761
Office Version
365, 2019, 2016
Platform
Windows
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
Joined
Sep 5, 2004
Messages
1,539
Office Version
2013, 2007
Platform
Windows
VBA Code:
=FIND("F",A2,FIND("F",A2)) - 1
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,539
Office Version
2013, 2007
Platform
Windows
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
Joined
Oct 10, 2011
Messages
4,535
Office Version
365
Platform
Windows
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
Joined
Aug 18, 2015
Messages
9,904
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
Joined
Dec 30, 2019
Messages
5
Office Version
2019
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top