Identify non-alphanumeric characters within a cell in Excel

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

<[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> <[email protected]> wrote in message
> news:[email protected]...
>>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.