reading non numeric text in a cell

[redneck joe]

SUM(IF(FREQUENCY(E10:E29,E10:E29)>0,1))

this is the formula I currently use to read employee numbers and it works when we just use the number i.e. 011004.


When we use the full employee number with alpha characters it does not work i.e. ASMO011004.



I have used helper cells to do similar, but am not wanting to do this way for simplicity reasons.


Any help?

[NBVC]

if the employee number is consistently 6 digits, then perhaps:

=SUM(IF(FREQUENCY(RIGHT(E10:E29,6)+0,RIGHT(E10:E29,6)+0)>0,1))

CSE confirmed

or if you want to use the whole code alphanumeric... try:

=SUMPRODUCT((E10:E29<>"")/COUNTIF(E10:E29,E10:E29&""))

ENTER only

[redneck joe]

or if you want to use the whole code alphanumeric... try:

=SUMPRODUCT((E10:E29<>"")/COUNTIF(E10:E29,E10:E29&""))

ENTER only

thank you!