I need to count a row with something in it
"RagDyer" wrote...
As long as your data has a *single* alpha character, and no spaces,
you could create a "helper" column to extract the numeric data,
and then just total that helper column.
With original data starting in A1,
try this in B1:
=IF(A10,--REPLACE(A1,MIN(SEARCH({"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"},A1&"abcdefghijklmnopqrstuvwx yz")),1,""),"")
....
Alternatively, if there'd never be more than 6 characters and all
numbers would be positive integers, you could do it with a single
formula.
=SUMPRODUCT(--(MID(rng,1,--ISNUMBER(-MID(rng,1,1)))
&MID(rng,2,--ISNUMBER(-MID(rng,2,1)))
&MID(rng,3,--ISNUMBER(-MID(rng,3,1)))
&MID(rng,4,--ISNUMBER(-MID(rng,4,1)))
&MID(rng,5,--ISNUMBER(-MID(rng,5,1)))
&MID(rng,6,--ISNUMBER(-MID(rng,6,1)))))
where rng is a placeholder for the range in question.
|