Sum numbers left of a character from within a string

[BarryTSL]

Please see attached sheet. I can count the number of occurances that a Variable is found in a string but need a formula that will find that variable and sum up only the first set of numbers to the left.

Thanks

[tom1977]

look into attachnent array formula

[BarryTSL]

Tom, thanks for the quick response, its close but not quite there. With the formula in WHI the result should be 10.5 not 2.

[BarryTSL]

Sorry, checking your formula I see I have misled slightly. I need a sum of the number to the left of that Letter not from the far left.

[tom1977]

Maybe upload new attachment with all expected results in every condition but in my opinion it will be rather hard to gain without VBA

[BarryTSL]

Please find attached workbook with expected results

Thanks

[DGagnon]

Try this array formula

=SUMPRODUCT((ISNUMBER(FIND(G3,$A$4:$E$4,1)))*(IFERROR(MID($A$4:$E$4,FIND(G3,$A$4:$E$4,1)-1,1),0)))

[BarryTSL]

Again it is close but not quite, the formula appears to be adding only the first number on the left ie. "1" and not the whole number ie. "10.5". I can see that the number of characters in the formula is set to 1 but I don't know how to make that a variable to suit all.

[DGagnon]

is VBA an option for this? or do you want to stick to a formula?

[BarryTSL]

VBA may be an option, I have a large number of cells I need the formula in but a pre-defined Function could work ok.

[davesexcel]

From your 1st example

' I can use a different separator " . " if it helps

With 4S.4H. you only want to use the 4
But
10.5WHI. you want to use the 10.5

Using a comma as a seperator would help a lot.

[DGagnon]

you could use this UDF, i changed the delimiter to ;

put this code in a new module

Please Login or Register  to view this content.

in your sheet use this syntax

=FindVarSum(G3,$A$4:$E$4)

[daddylonglegs]

With comma separator try this formula in G4

=COUNTIF($A$4:$E$4,"*"&G3&",*")

then this "array formula" in H4

=SUM(IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT($A4:$E4,FIND(G3&",",$A4:$E4)-1),",",REPT(" ",9)),9))+0,0))

confirmed with CTRL+SHIFT+ENTER

....and then drag both of those across, see attached

[BarryTSL]

Thank you very much, would not of got there without you. I will go with the Function option as this is a lot easier to input.

[snb]

or

Please Login or Register  to view this content.

in a cell

=freq_snb($A$4:$E$4;G3) 
 
=sum_snb($A$4:$E$4;G3)