Excel VBA - Cells Counting Problem

[wuming]

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(A14,"*") 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

 

[John]

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

 

[wuming]

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

 

[Norman Jones]

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

 

[wuming]

thanks for the help Norman!

 

[wuming]

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!!! :

 

[Norman Jones]

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.