# identifying alphanumerics in a column

I have a column that usually has 8 digit values in it.  these can be alphanumeric so a greater than function won't work.

i need to identify any cell in this column that has 9 digits or more.
any ideas?
###### Who is Participating?

Older than dirtCommented:
There may well be a native Excel formula but here's a User Defined Function you can use just like a formula.

Usage:
=HasNine(A1)
``````Function HasNine(cel As Range) As Boolean
Dim intChar As Integer
Dim intCount As Integer
For intChar = 1 To Len(cel.Value)
If IsNumeric(Mid\$(cel.Value, intChar, 1)) Then
intCount = intCount + 1
End If
Next
If intCount > 8 Then
HasNine = True
End If
End Function
``````
0

Commented:
So they are alphanumeric. Do you want to identify more than 8 characters (letters and digits combined)? Or do you mean that you want to identify those that have more than 8 digits (0-9) in the alphanumeric string, regardless of its total length?
0

Commented:
Also, are your cells formatted General or Text?
0

Older than dirtCommented:
If instead you want to know if the value of numbers in a cell is greater than a certain value then use this, where True is returned for values > 999,999,999.
``````Function GreaterThanMinimum(cel As Range) As Boolean
Const LIMIT As Double = 999999999
Dim strNumbers As String
Dim intChar As Integer

For intChar = 1 To Len(cel.Value)
If IsNumeric(Mid\$(cel.Value, intChar, 1)) Then
strNumbers = strNumbers & Mid\$(cel.Value, intChar, 1)
End If
Next
If CDbl(strNumbers) > LIMIT Then
GreaterThanMinimum = True
End If
End Function
``````
0

Older than dirtCommented:
Or this version where you can specify the lower limit in the formula.
Usage for finding values > 4000:
=GreaterThanMinimum(A1,4000)

or
=GreaterThanMinimum(A1)
for finding values of 1 million or more

``````Function GreaterThanMinimum(cel As Range, Optional LIMIT As Double = 999999999) As Boolean
Dim strNumbers As String
Dim intChar As Integer

For intChar = 1 To Len(cel.Value)
If IsNumeric(Mid\$(cel.Value, intChar, 1)) Then
strNumbers = strNumbers & Mid\$(cel.Value, intChar, 1)
End If
Next
If CDbl(strNumbers) > LIMIT Then
GreaterThanMinimum = True
End If
End Function
``````
0

Older than dirtCommented:
I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.