Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 59
  • Last Modified:

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?
0
jamesmetcalf74
Asked:
jamesmetcalf74
  • 4
  • 2
1 Solution
 
Missus Miss_SellaneusCommented:
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
 
Missus Miss_SellaneusCommented:
Also, are your cells formatted General or Text?
0
 
Martin LissRetired ProgrammerCommented:
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

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Martin LissRetired ProgrammerCommented:
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

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
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

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now