Counting Numbers with Text

[scottymelloty]

Hi,

Is it possible to count how many top 10's there have been in a row o
data that has text with the numbers ie:

jp5 ,, jp23 ,, as6 ,, as50 ,, as100 ,, 5 is this data was in a row o
the spreadsheet i want it to return that there were 3 entries that wer
10 or under

someone gave me thsu formula to colour using conditional formatting fo
a similar problem i had if that helps

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT
("1:100")),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT
("1:100")),1))))<1

 

[Guest]

Assuming the data is in a1 to e1
=SUM(IF(--MID(A1:E1,3,LEN(A1:E1))<10,1,0))

This is an array formula that must be entered with ctrl|shift|enter

Note: there were only 2 entries under 10

Lance

 

[scottymelloty]

Thanks for that,
i cant get the formula to work but what i didnt tell you is that som
cells will be blank and some cells will just have a number in withou
text so thats probably why,

how can i adapt the formula to allow for that

Many Thanks

 

[scottymelloty]

Sorry another thing i should mention is that this is the full list o
letters available before a number some are single letters

e
u
as
jp
nz
vn
ct
nt
and just numbers on there own

Thanks

 

[Domenic]

Assuming that Column A contains your data, enter the following arra
formula in B1 and copy down:

=--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255)

...confirmed with CONTROL+SHIFT+ENTER.

Then, use COUNTIF to get your desired result...

=COUNTIF(B:B,"<=10")

Hope this helps!

 

[scottymelloty]

Thanks for all the help guys, much appreciated !!! Will save me a lot o
time

 

[Myrna Larson]

I counted 3: jp5, as6, and 5.

Assuming the data is in a1 to e1
=SUM(IF(--MID(A1:E1,3,LEN(A1:E1))<10,1,0))

This is an array formula that must be entered with ctrl|shift|enter

Note: there were only 2 entries under 10

Lance