tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Excel Functions

(Scroll to see more)

Share this page:

MS Excel: Test a string for an alphanumeric value

Question: In Microsoft Excel, I want to know if a string value contains alphanumeric characters only. How can I do this?

Answer: To accomplish this, you need to create a custom function.

You'll need to open your Excel spreadsheet. Press Alt-F11 and create a new module.

Then paste into the new module the following function:

Function AlphaNumeric(pValue) As Boolean

   Dim LPos As Integer
   Dim LChar As String
   Dim LValid_Values As String
   
   'Start at first character in pValue
   LPos = 1
   
   'Set up values that are considered to be alphanumeric
   LValid_Values = " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789"
   
   'Test each character in pValue
   While LPos <= Len(pValue)
   
      'Single character in pValue
      LChar = Mid(pValue, LPos, 1)
      
      'If character is not alphanumeric, return FALSE
      If InStr(LValid_Values, LChar) = 0 Then
         AlphaNumeric = False
         Exit Function
      End If
      
      'Increment counter
      LPos = LPos + 1
   
   Wend
   
   'Value is alphanumeric, return TRUE
   AlphaNumeric = True
   
End Function

The AlphaNumeric function will return TRUE if all of the values in the string are alphanumeric. Otherwise, it will return FALSE.

Microsoft Excel

Based on the spreadsheet above, the following Excel AlphaNumeric examples would return:

=AlphaNumeric(A1) would return TRUE
=AlphaNumeric(A2) would return FALSE
=AlphaNumeric("6.49") would return TRUE
=AlphaNumeric("^Tech on the Net ") would return FALSE