i know that to count the number of cells that contain any text you ca
use:
=COUNTIF(data,"*")

what if i want to differentiate the data as alphabetic data an
alphanumeric data?
eg. COUNTIF(A1:D4,"*") returns 16 when all cells have data like:
abc125 or abc

How do u differentiate data as alphanumeric (abc125) and alphabethi
(abc) data?? PLs help me

--
Message posted from http://www.ExcelForum.com

if the length of the numeric part is fixed (say 3 digits)
you can break the text string into the numeric part and
then take the value of it and do a countif(range,"0") on
that

=value(right(cell,3) will give you the value of the 3
digits in the 3 rightmost positions of the cell (assuming
there are 3 digits of course). If the cell has no digits,
the result of the expression wil be zero and not picked up
by the countif.

John


-----Original Message-----
i know that to count the number of cells that contain any
text you can
use:
=COUNTIF(data,"*")

what if i want to differentiate the data as alphabetic
data and
alphanumeric data?
eg. COUNTIF(A1:D4,"*") returns 16 when all cells have
data like:
abc125 or abc

How do u differentiate data as alphanumeric (abc125) and
alphabethic
(abc) data?? PLs help me?


---
Message posted from http://www.ExcelForum.com/

.

thanks for replying john. But the data i am counting are not fixe
meaning the codes wouldn't work, do u have other solutions?
abc125 can also be xxoo09496 or the data can vary

--
Message posted from http://www.ExcelForum.com

Hi Wumin,

To return the number of alpha cells, try the following function:

Function CountAlphas(CountRange As Range) As Long
Dim cell As Range
Dim iCtr As Long

For Each cell In CountRange
If Not IsEmpty(cell) Then
If Not cell.Value Like "*[0-9]*" Then
iCtr = iCtr + 1
End If
End If
Next
CountAlphas = iCtr
End Function


---
Regards,
Norman



"wuming " wrote in message
...
i know that to count the number of cells that contain any text you can
use:
=COUNTIF(data,"*")

what if i want to differentiate the data as alphabetic data and
alphanumeric data?
eg. COUNTIF(A1:D4,"*") returns 16 when all cells have data like:
abc125 or abc

How do u differentiate data as alphanumeric (abc125) and alphabethic
(abc) data?? PLs help me?


---
Message posted from http://www.ExcelForum.com/

thanks for the help Norman!

--
Message posted from http://www.ExcelForum.com

sry to bother u guys again but i have a problem:
i need to use the formula to count cells of up to 60k records++
and after using the codes given by Norman, the excel file hange
meaning that the codes cannot work for huge amount of datas. Do u guy
have any other alternatives?

its giving me a headache!!! :

--
Message posted from http://www.ExcelForum.com

Hi Wuming,

I called the function from a sub, using a range in excess of 16 million
cells. The only problem I experienced was the execution time.

I called the function from a worksheet cell, passing a range in excess of 1
million cells to the function. Again no problem except execution time.

Finally, using your problem example, I called the function from a worksheet
cell, passing the function a range of 61k cells. The function returned a
value in a fraction of a second.

I am sorry, therefore, that I cannot reproduce your problem.


---
Regards,
Norman

"wuming " wrote in message
...
sry to bother u guys again but i have a problem:
i need to use the formula to count cells of up to 60k records++
and after using the codes given by Norman, the excel file hanged
meaning that the codes cannot work for huge amount of datas. Do u guys
have any other alternatives?

its giving me a headache!!! :(


---
Message posted from http://www.ExcelForum.com/