Hightlight cells that contain special characters
Try this UDF:
Function standchr(instring)
standchr = True
For i = 1 To Len(instring)
If Not (LCase(Mid(instring, i, 1)) = "a" And LCase(Mid(instring, i,
1)) <= "z") Then
standchr = False
Exit For
End If
Next i
End Function
Apply conditional formatting with formula
=NOT( standchr(A1))
--
Regards!
Stefi
€˛Gareth_Evans (InterCall EMEA)€¯ ezt Ć*rta:
Hi there, I have a spreadsheet containing first & last names (columns A & B),
up to 10,000 rows and some of the names contain 'foreign' characters - such
as Ć©.
I would like to be able to hightlight all cells that contain a non-standard
character (ie: A-Z, a-z, 0-9) which will allow our admin team to eailly
filter and correct before the data is loaded into the system.
I've been trying to create a macro for this but I can't seem to get a
formula together to include for all the possible special characters.
I'm ok with excel but by no means a whizz, if anyone can offer any advice I
would be most grateful.
Best regards,
Gareth
|