View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.newusers
pmartglass pmartglass is offline
external usenet poster
 
Posts: 42
Default Count Cells with alphanumeric data

Thanks - every way I could think of required a helper column as well
thanks for the input

"JLatham" wrote:

Someone sharper than me may come up with a one-formula solution, but about
the best I can do is recommend using a helper column.

In the helper column (assume you have 1st entry, like asd, in A1) on row 1,
put this formula:
=COUNT(1*MID(A1,ROW($1:$9),1))
and 'commit' (end) the formula by pressing the [Shift]+[Ctrl]+[Enter] key to
make it an array formula. Fill it down as far as required. make the $9 =
the longest possible string in your list that it may find. This formula will
return the number of numeric characters found in each string. Now you can
use this formula to get a count of entries in A that numbers in them (this
assumes that your helper column with the array formula from above is in
column X)
=COUNTIF(X:X,"0")

Hope that helps.

"pmartglass" wrote:

That works great if the number is at the beginning of the string, is there a
way to do it if the number is anyplace in the string ?

"JLatham" wrote:

If all of the ones with number have a number as the 1st character, then a
formula like this should work, just change the A1:A8 range to whatever range
you need:
=COUNTIF(A1:A8,"<A")


"Aris" wrote:

Hi,

I'm just trying to count the cells in a range that have alphanumeric data.

example: asd, we, rty, 22r, 45aa, 1s, wer, tyu.

I need to count all cells in that range that have numbers.