How to eliminate rows with foreign text characters ?

**Mark53** · 05-18-2011, 03:23 PM

Hi, people,

Using Excel 2007,
I have one column of about 1500 people's first names.
Some include foreign characters, like the "e" with a slant over it.
I want to eliminate/delete all rows that have anything except
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ
in them. How to do that, please?

Thanks.

Mark53

**NBVC** · 05-18-2011, 03:51 PM

Possibly:

In a new column try formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER, where A2 contains first original string.

Copy down.

Then filter this column through Data|Filter|Autofilter for TRUE and delete those rows.

**Mark53** · 05-18-2011, 04:40 PM

Thanks for the reply, NBVC, but it ain't workin'.
They All come up as TRUE.
The names are in Column D, starting in Row 1, so I put D1 twice in your formula instead of A2.

**NBVC** · 05-18-2011, 04:59 PM

Try:

=OR(ISNA(MATCH(MID(UPPER(D1),ROW(INDIRECT("1:"&LEN(D1))),1),CHAR(ROW(INDIRECT("65:90"))),0)))

confirmed with CTRL+SHIFT+ENTER

This assumes one word in each cell....

If there are spaces you want to ignore then...

=OR(ISNA(MATCH(MID(UPPER(SUBSTITUTE(D1," ","")),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(D1," ","")))),1),CHAR(ROW(INDIRECT("65:90"))),0)))

**Mark53** · 05-18-2011, 05:35 PM

I have Very little understanding of that formula, but It WORKed !
THANKS Very Much. NBVC.
I couldn't do the "Data|Filter" thing, so I just Sorted that column.
All is well. Life is good. There is peace on earth. OK,... not really.

Thanks.

Mark53