Identify non-alphanumeric characters within a cell in Excel

  • Thread starter Thread starter cmotes
  • Start date Start date
C

cmotes

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
 
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
 
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.
 
Back
Top