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

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

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

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

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

"Stefi" wrote:

> 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

�  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.

On Thu, 4 Feb 2010 06:01:01 -0800, Gareth_Evans (InterCall EMEA)
<GarethEvansInterCallEMEA@discussions.microsoft.com> wrote:

>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

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

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

"Stefi" wrote:

> 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

On Fri, 5 Feb 2010 01:54:01 -0800, Gareth_Evans (InterCall EMEA)
<GarethEvansInterCallEMEA@discussions.microsoft.com> wrote:

>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

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

On Fri, 5 Feb 2010 01:54:01 -0800, Gareth_Evans (InterCall EMEA)
<GarethEvansInterCallEMEA@discussions.microsoft.com> wrote:

>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

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

On Tue, 9 Feb 2010 13:45:01 -0800, Gareth_Evans (InterCall EMEA)
<GarethEvansInterCallEMEA@discussions.microsoft.com> wrote:

>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

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

On Tue, 9 Feb 2010 13:45:01 -0800, Gareth_Evans (InterCall EMEA)
<GarethEvansInterCallEMEA@discussions.microsoft.com> wrote:

>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

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:

> 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