I need a macro in Excel that will:
(a) identify non-alphnumeric characters within a cell,
(b) within range A1:G60000
(c) and mark each cell with such a character in a way that I can easily
identify the cells with FIND or by sorting

I found this macro here but all it does is change the color of the
non-alphanumeric characters. I have 180,000 rows to look through so I
need a way to aggregate all of the exceptions in a short list so I can
deal with them.

Sub Test()
For Each cell In Range("A1:G50000").Cells
For i = 1 To Len(cell)
Select Case Asc(Mid(cell.Value, i, 1))
Case 48 To 57, 65 To 90, 79 To 122
cell.Characters(Start:=i, Length:=1).Font.ColorIndex =
1
Case Else
cell.Characters(Start:=i, Length:=1).Font.ColorIndex =
3
End Select
Next i
Next
End Sub

Thanks for your help!

Any use:
Sub Test()
For Each cell In Range("A1:G50").Cells
For i = 1 To Len(cell)
Select Case Asc(Mid(cell.Value, i, 1))
Case 48 To 57, 65 To 90, 79 To 122
' cell.Characters(Start:=i, Length:=1).Font.ColorIndex = 1
Case Else
cell.Value = "test " & cell.Value
End Select
Next i
Next
End Sub

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
I need a macro in Excel that will:
(a) identify non-alphnumeric characters within a cell,
(b) within range A1:G60000
(c) and mark each cell with such a character in a way that I can easily
identify the cells with FIND or by sorting

I found this macro here but all it does is change the color of the
non-alphanumeric characters. I have 180,000 rows to look through so I
need a way to aggregate all of the exceptions in a short list so I can
deal with them.

Sub Test()
For Each cell In Range("A1:G50000").Cells
For i = 1 To Len(cell)
Select Case Asc(Mid(cell.Value, i, 1))
Case 48 To 57, 65 To 90, 79 To 122
cell.Characters(Start:=i, Length:=1).Font.ColorIndex =
1
Case Else
cell.Characters(Start:=i, Length:=1).Font.ColorIndex =
3
End Select
Next i
Next
End Sub

Thanks for your help!

Alternative:
Sub Test2()
For Each cell In Range("A1:G50").Cells
check = 0
For i = 1 To Len(cell)
Select Case Asc(Mid(cell.Value, i, 1))
Case 48 To 57, 65 To 90, 79 To 122
check = check
Case Else
check = check + 1
End Select
Next i
If check 0 Then cell.Value = "test " & cell.Value
Next
End Sub


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
Any use:
Sub Test()
For Each cell In Range("A1:G50").Cells
For i = 1 To Len(cell)
Select Case Asc(Mid(cell.Value, i, 1))
Case 48 To 57, 65 To 90, 79 To 122
' cell.Characters(Start:=i, Length:=1).Font.ColorIndex = 1
Case Else
cell.Value = "test " & cell.Value
End Select
Next i
Next
End Sub

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
I need a macro in Excel that will:
(a) identify non-alphnumeric characters within a cell,
(b) within range A1:G60000
(c) and mark each cell with such a character in a way that I can easily
identify the cells with FIND or by sorting

I found this macro here but all it does is change the color of the
non-alphanumeric characters. I have 180,000 rows to look through so I
need a way to aggregate all of the exceptions in a short list so I can
deal with them.

Sub Test()
For Each cell In Range("A1:G50000").Cells
For i = 1 To Len(cell)
Select Case Asc(Mid(cell.Value, i, 1))
Case 48 To 57, 65 To 90, 79 To 122
cell.Characters(Start:=i, Length:=1).Font.ColorIndex =
1
Case Else
cell.Characters(Start:=i, Length:=1).Font.ColorIndex =
3
End Select
Next i
Next
End Sub

Thanks for your help!

This worked well, thank you.

I didn't define my problem perfectly: I wanted it to ignore spaces,
dashes and apostrophes. So I just did FIND/REPLACE on those characters
and this worked perfectly. Thanks for saving me a ton of time.