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.

--
Regards,
Tom Ogilvy



"KLZA" wrote:

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

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

On Aug 9, 2:55 pm, KLZA wrote:
Hi. Is there a simple validation formula that would restrict a cell
to alpha-numerics only and exclude all other characters? Thanks in
advance. :)

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

Regards,
Peter T


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

On Aug 9, 2:55 pm, KLZA wrote:
Hi. Is there a simple validation formula that would restrict a cell
to alpha-numerics only and exclude all other characters? Thanks in
advance. :)

yes, 0-9 and a-z only...

On Aug 9, 5:06 pm, "Peter T" <peter_t@discussions wrote:
What about non Latin and/or accented characters, to clarify do you mean only
[A-Z, a-z, 0-9].

Regards,
Peter T

"KLZA" wrote in message

oups.com...



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

On Aug 9, 2:55 pm, KLZA wrote:
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 -

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" wrote in message
oups.com...
yes, 0-9 and a-z only...

On Aug 9, 5:06 pm, "Peter T" <peter_t@discussions wrote:
What about non Latin and/or accented characters, to clarify do you mean
only
[A-Z, a-z, 0-9].

Regards,
Peter T

"KLZA" wrote in message

oups.com...



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

On Aug 9, 2:55 pm, KLZA wrote:
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 -

Typo in the Worksheet_Change routine

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

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
oups.com...
yes, 0-9 and a-z only...

On Aug 9, 5:06 pm, "Peter T" <peter_t@discussions wrote:
What about non Latin and/or accented characters, to clarify do you
mean
only
[A-Z, a-z, 0-9].

Regards,
Peter T

"KLZA" wrote in message

oups.com...



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

On Aug 9, 2:55 pm, KLZA wrote:
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 -