VALIDATION USING ALPHA-NUMERICS ONLY - NO SPECIAL CHARACTERS

  • Thread starter Thread starter KLZA
  • Start date Start date
K

KLZA

Hi. Is there a simple validation formula that would restrict a cell
to alpha-numerics only and exclude all other characters? Thanks in
advance. :)
 
for these entries entered as text:
is "False" an alphanumeric. Is "123" an alphanumeric? Is "1 Jan 2001" an
alpha numeric? Is anything preceded with a single quote an alphanumeric?
Is "%$#^" an alphanumeric?

Could you be more precise in your description of what an alpha numeric is in
your definition.
 
By alphanumeric i mean letters (alpha) and numbers (numerics) only..
no !@#$%^&*(*) <---- not a curse word :) etc... thanks!
 
What about non Latin and/or accented characters, to clarify do you mean only
[A-Z, a-z, 0-9].

Regards,
Peter T
 
yes, 0-9 and a-z only...

What about non Latin and/or accented characters, to clarify do you mean only
[A-Z, a-z, 0-9].

Regards,
Peter T




By alphanumeric i mean letters (alpha) and numbers (numerics) only..
no !@#$%^&*(*) <---- not a curse word :) etc... thanks!
Click to expand...

- Show quoted text -
Click to expand...
 
I don't think you can do that with data validation, would need to process in
a worksheet change event, or flag an adjacent cell with a UDF.

Have a go with this (there are other radically different approaches) -

'' code in the Worksheet module
'' right click sheet tab > view code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim s As String
Dim rng As Range
Dim cel As Range
Dim byArr() As Byte
Static bRep As Boolean

If bRep Then
bRep = False
Exit Sub
End If

' traps all changing text cells on the sheet
' if necessary adapt if only want to process certain cell(s) or area(s)

If Target.Count = 1 Then
If Not Target.HasFormula And Len(Target) Then
If Not IsNumeric(Target) Then Set rng = Target
End If
Else
On Error Resume Next
Set rng = Target.SpecialCells(xlCellTypeConstants, 2)
End If

On Error GoTo errExit
If Not rng Is Nothing Then

For Each cel In Target.Cells

byArr = cel.Text

For i = 0 To UBound(byArr) Step 2
If byArr(i + 1) Then
'some char's with code 127+ have value in the second byte
'set the 1st byte value to code 35, a "#"
byArr(i) = 35
'clear the 2nd byte
byArr(i + 1) = 0
bRep = True
Else
Select Case byArr(i)
Case 32, 48 To 57, 65 To 90, 97 To 122
'space, 0-9, A-Z, a-z do nothing
Case Else
' set the byte value to code 35, a "#"
byArr(i) = 35
bRep = True
End Select
End If
Next

If bRep Then
s = byArr
cel.Value = Replace(s, "#", "")
bRep = False
End If

Next

End If
errExit:

End Sub

Code assumes you also want to allow spaces, if not remove 32, in the select
case.

'' in a normal module
Sub SampleText()
Dim i&, s$
For i = 33 To 255
s = s & Chr(i)
Next
Selection = s
End Sub


Regards,
Peter T


KLZA said:
yes, 0-9 and a-z only...

What about non Latin and/or accented characters, to clarify do you mean only
[A-Z, a-z, 0-9].

Regards,
Peter T




By alphanumeric i mean letters (alpha) and numbers (numerics) only..
no !@#$%^&*(*) <---- not a curse word :) etc... thanks!
Click to expand...
Hi. Is there a simple validation formula that would restrict a cell
to alpha-numerics only and exclude all other characters? Thanks in
advance. :)- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 
Typo in the Worksheet_Change routine

change -
For Each cel In Target.Cells
Click to expand...
to -
For Each cel In rng.Cells

Regards,
Peter T

Peter T said:
I don't think you can do that with data validation, would need to process in
a worksheet change event, or flag an adjacent cell with a UDF.

Have a go with this (there are other radically different approaches) -

'' code in the Worksheet module
'' right click sheet tab > view code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim s As String
Dim rng As Range
Dim cel As Range
Dim byArr() As Byte
Static bRep As Boolean

If bRep Then
bRep = False
Exit Sub
End If

' traps all changing text cells on the sheet
' if necessary adapt if only want to process certain cell(s) or area(s)

If Target.Count = 1 Then
If Not Target.HasFormula And Len(Target) Then
If Not IsNumeric(Target) Then Set rng = Target
End If
Else
On Error Resume Next
Set rng = Target.SpecialCells(xlCellTypeConstants, 2)
End If

On Error GoTo errExit
If Not rng Is Nothing Then

For Each cel In Target.Cells

byArr = cel.Text

For i = 0 To UBound(byArr) Step 2
If byArr(i + 1) Then
'some char's with code 127+ have value in the second byte
'set the 1st byte value to code 35, a "#"
byArr(i) = 35
'clear the 2nd byte
byArr(i + 1) = 0
bRep = True
Else
Select Case byArr(i)
Case 32, 48 To 57, 65 To 90, 97 To 122
'space, 0-9, A-Z, a-z do nothing
Case Else
' set the byte value to code 35, a "#"
byArr(i) = 35
bRep = True
End Select
End If
Next

If bRep Then
s = byArr
cel.Value = Replace(s, "#", "")
bRep = False
End If

Next

End If
errExit:

End Sub

Code assumes you also want to allow spaces, if not remove 32, in the select
case.

'' in a normal module
Sub SampleText()
Dim i&, s$
For i = 33 To 255
s = s & Chr(i)
Next
Selection = s
End Sub


Regards,
Peter T


KLZA said:
yes, 0-9 and a-z only...
Click to expand...
mean
only
[A-Z, a-z, 0-9].

Regards,
Peter T





By alphanumeric i mean letters (alpha) and numbers (numerics) only..
no !@#$%^&*(*) <---- not a curse word :) etc... thanks!

Hi. Is there a simple validation formula that would restrict a cell
to alpha-numerics only and exclude all other characters? Thanks in
advance. :)- Hide quoted text -

- Show quoted text -
Click to expand...
Click to expand...
Click to expand...
 
Back
Top