Find special characters

lanka123

New Member
Joined
Apr 20, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Hello! I have a list of company names and some contain the accented characters (like á). I created a separate sheet with a column where all possible accented (foreign) characters are listed and sorted.
I need to search the company list for the presence of any of the special characters in each company name, and then mark it. (Then I'll remove these records from the excel as our program doesn't recognize them and we need to load most of the file into the system before we can fix the code) Thank you.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,929
Office Version
  1. 2016
Platform
  1. Windows
Hi Lanka123,

You could highlight with Conditional Formatting

Book1
ABCDE
1DataBad Letters
2One here áá
3And à here.à
4None in $£"&^*()!@~#ä
5More than one here ô àé
6None hereô
7And ä here.
8And é here.
9And ô here.
10é
11None here
12à
13
14
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A9999Expression=IFERROR(AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($E$2:$E$99<>"")*(MATCH("*"&$E$2:$E$99&"*",$A2,0))),1),FALSE)textNO
 

lanka123

New Member
Joined
Apr 20, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Thank you, so I am supposed to use option 'new rule' in cond formatting and then option - 'Use a formula to determine which cells to format', right?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

You can also do this, to Flag or CF:

Book3.xlsx
ABCDE
1DataTo FlagTo CFBad Letters
2One here áFlag1á
3And à here.Flag1à
4None in $£"&^*()!@~# 0ä
5More than one here ô àFlag2é
6None here 0ô
7And ä here.Flag1
8And é here.Flag1
9And ô here.Flag1
10éFlag1
11None here 0
12àFlag1
Sheet917
Cell Formulas
RangeFormula
B2:B12B2=IF(SUMPRODUCT(--ISNUMBER(FIND(E$2:E$6,A2))),"Flag","")
C2:C12C2=SUMPRODUCT(--ISNUMBER(FIND(E$2:E$6,A2)))
 
Solution

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,929
Office Version
  1. 2016
Platform
  1. Windows
Yes, and of course change the cell ranges as appropriate for your data.
 

Forum statistics

Threads
1,141,705
Messages
5,707,976
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top