PDA

View Full Version : Finding special charecters in excel



waseem.aman
02-08-2011, 02:19 AM
Hi Guys,

I have milions of records in excel in which in need to check is any special charecter is present in this milions records.

Example record 1

Name |Adress 1|Addess 2|Phone number
Amer|&102!!!|bangalore|98000!!!&***

phone number should be always integer.. so i need formula or Vba code to identify if any special charecter present in it

mancubus
02-08-2011, 03:28 AM
what is a special char for you?
all chars other than (A-Z, a-z, 0-9)?

waseem.aman
02-08-2011, 03:31 AM
Yes all chars other than (A-Z, a-z? 0-9).

mancubus
02-08-2011, 03:34 AM
to see special chars, open a blank worksheet, start from cell A1, type "=CHAR(ROW())", and copy this formula to A2-A255.

char 48 - char 57 = 0 - 9
char 65 - char 90 = A - Z
char 97 - char 122 = a - z

mancubus
02-08-2011, 03:37 AM
here is what get via google:

Option Explicit
Sub SpecialChars()
Dim RangeToCheck As Range, c As Range

Set RangeToCheck = Range("A1:B10000")
For Each c In RangeToCheck
If Len(c.Text) > 0 Then
If c.Text Like "*[!.A-Za-z0-9]*" Then
c.Interior.Color = vbRed
Else: c.Interior.Color = vbYellow
End If
End If
Next c
End Sub




Sub Color_Non_Standard()
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Not Mid(rngR.Value, intI, 1) Like "[A-Z,a-z,0-9,.]" Then
rngR.Interior.ColorIndex = 3
End If
Next intI
Next rngR
End Sub


http://www.eggheadcafe.com/software/aspnet/35664281/hightlight-cells-that-contain-special-characters.aspx

waseem.aman
02-08-2011, 03:47 AM
Thank you very much looking for the same code.