Hightlight cells that contain special characters

[Gareth_Evans (InterCall EMEA)]

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

 

[Stefi]

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:

 

[Gareth_Evans (InterCall EMEA)]

I've managed to get the following VBA code from another user and ameded
slightly for my use - this brings up a text box which shows the cell
reference and contents in a pop up message box.

Is there a way I can replace "'find é in text" with a generic statement
which checks for all characters that aren't A-Z, a-z 0-9? The only other
alternative would be to type them all out?

How can I highlight a cell containing one of these un-required characters
with a yellow background (or any colour) instead of it popping up in a
message box?

Best regards,

Gareth

Sub FindCellsWithAsterisks()
'find é in text
Dim cell As Range, FirstAddress As String, FoundList As String
With ActiveSheet.UsedRange
'use tilde to find é
Set cell = .Find("~é", LookIn:=xlValues, SearchOrder:=xlByRows, _
LookAt:=xlPart)
If Not cell Is Nothing Then
FirstAddress = cell.Address '< Bookmark start point
Do
FoundList = FoundList & "Cell " & cell.Address(0, 0) & _
" =" & vbTab & cell & vbNewLine
Set cell = .FindNext(cell)
Loop Until cell Is Nothing Or cell.Address = FirstAddress
End If
End With
'show search results
MsgBox FoundList
Set cell = Nothing
End Sub

 

[Gareth_Evans (InterCall EMEA)]

Thanks for this one Steffi, for some reason it keeps erroring out

'If not.....' line is in red, as is the 1)) and the "Apply" string and the
=not.

I tried putting the ' infront of the commands but it's not running. I'm not
exactly sure what I'm doing wrong, I'm a bit of a novice at VBA.

Best regards,

Gareth

 

[Gareth_Evans (InterCall EMEA)]

Hi Stefi, I've managed to get this working (not so much the conditional
format section though), the VBA had a return value in that I removed and it's
all ok.
Thanks for the suggestion, it goes some way to sorting this out but it's not
exactly what I was after.

If anyone else has any other suggestions on this, I've googled all day and
there are folks who want to identify these characters (but with varying end
reults, most want to auto replace or remove but I don't).

I'm sure it's a pretty simple code but it may take a while to get all the
characters written in. If this is the case, if someone can start me off I'll
happily carry on

Many thanks,

Gareth

 

[Gord Dibben]

é is not really a "foreign" character.

You say "standard characters that aren't A-Z, a-z, 0-9"

What about other standard chars like , . ? " ' ( ) $ % @ * /

In a blank sheet in A1 enter =CHAR(ROW()) and copy down to A255 and see the
characters produced.

Which of those characters would you consider as "foreign"?

Perhaps we can narrow it down to a series like char(192) through char(255)


Gord Dibben MS Excel MVP


Taking you literally.....use this macro but you will get many hits that are
not "foreign" characters.

 

[Gareth_Evans (InterCall EMEA)]

Hi Gord, thank you for your reply and sorry for not defining correctly.

The name strings in the two columns, first & last name, can't contain any
characters other than A-Z, a-z, 0-9. No accented characters or symbols. I'd
like to identify these entries with some type of marker so they can be
manually checked.

I'm not looking to auto replace them, just identify them - possibly with a
cell highlight but open to suggestions.

The system we load these names into doesn't support uni-code (yet) and we
get failures on large sheets due to this (we sometimes miss them on the
manual check).

From your instructions I've used =CHAR(ROW()), it's probably easier to list
the one's we'd like to allow as this is a smaller list.

Rows: 46 (full stop), 48-57 (0-9), 65-90 (A-Z) and 97-122 (a-z)

Not quite a sequential series, but could these be grouped maybe?

Thank you for your time, it really is appreciated.

Kind regards,

Gareth

 

[Gareth_Evans (InterCall EMEA)]

Hey Stefi - I got your suggestion working, I don't know what I was doing
wrong but your idea was perfect - spot on! Thank you

Is there a way I can modify the UDF to allow the following?

A space
A hyphon (-)

Warmest regards,

Gareth

 

[Ron Rosenfeld]

Hi Gord, thank you for your reply and sorry for not defining correctly.

The name strings in the two columns, first & last name, can't contain any
characters other than A-Z, a-z, 0-9. No accented characters or symbols. I'd
like to identify these entries with some type of marker so they can be
manually checked.

I'm not looking to auto replace them, just identify them - possibly with a
cell highlight but open to suggestions.

The system we load these names into doesn't support uni-code (yet) and we
get failures on large sheets due to this (we sometimes miss them on the
manual check).

From your instructions I've used =CHAR(ROW()), it's probably easier to list
the one's we'd like to allow as this is a smaller list.

Rows: 46 (full stop), 48-57 (0-9), 65-90 (A-Z) and 97-122 (a-z)

Not quite a sequential series, but could these be grouped maybe?

Thank you for your time, it really is appreciated.

Kind regards,

Gareth

Perhaps:

===========================
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
===============================

And if there are any other characters, such as a <space> or hyphen or
apostrophe that might be included, you can modify the [!charlist] token above
to include them.
--ron

 

[Gord Dibben]

Per your request. 0-9, a-z, A-Z and full stop.

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


Gord

 

[Gareth_Evans (InterCall EMEA)]

Stefi, Gord and Ron - thank you all so much for your replies. This has
inspired me to learn VBA properly and write a script that will help one of
our teams even more.

With this sheet I plan to have the cells which contain anything other than
A-Z/0-9/space/Hyphen/' show in red.
Cells in certain columns which can only have a max amount of characters
highlight in blue, an email validation check (for basic format) showing in
red also and to turn all cell contents into 'Case Text'.

I've even got my workbook to close without giving the user an option to save
changes!! I know this is all basic stuff to the pro's but I was so impressed


Thanks again and, if anyone reads this and can recommend helpful reference
material/books for a beginner I'd love to hear from them.

Very best regards,

Gareth

 

[Ron Rosenfeld]

Stefi, Gord and Ron - thank you all so much for your replies. This has
inspired me to learn VBA properly and write a script that will help one of
our teams even more.

With this sheet I plan to have the cells which contain anything other than
A-Z/0-9/space/Hyphen/' show in red.
Cells in certain columns which can only have a max amount of characters
highlight in blue, an email validation check (for basic format) showing in
red also and to turn all cell contents into 'Case Text'.

I've even got my workbook to close without giving the user an option to save
changes!! I know this is all basic stuff to the pro's but I was so impressed


Thanks again and, if anyone reads this and can recommend helpful reference
material/books for a beginner I'd love to hear from them.

Very best regards,

Gareth

Glad to help. Thanks for the feedback.

Hopefully, one of the others will have some references for you.
--ron

 

[Gord Dibben]

Thanks for the feedback.

As far as books go..................

See Debra Dalgleish's site for a list of books.

http://www.contextures.on.ca/xlbooks.html

Under Functions, anything by John Walkenbach

Under VBA, any of the first three listed and any other by John Walkenbach.

Also Reed Jacobsen books are generally pretty good in my experience.


Gord Dibben MS Excel MVP

 

[Stefi]

You are welcome! Thanks for the feedback!

I'd suggest to make use of macro recording, I found it a very useful mean -
together with reading Help - when learning VBA.

--
Regards!
Stefi



„Gareth_Evans (InterCall EMEA)†ezt írta: