Find special characters

[lanka123]

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.

 

[Toadstool]

Hi Lanka123,

You could highlight with Conditional Formatting

Book1
	A	B	C	D	E
1	Data				Bad Letters
2	One here á				á
3	And à here.				à
4	None in $£"&^*()!@~#				ä
5	More than one here ô à				é
6	None here				ô
7	And ä here.
8	And é here.
9	And ô here.
10	é
11	None here
12	à
13
14
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A9999	Expression	=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)	text	NO

 

[lanka123]

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]

Hi,

You can also do this, to Flag or CF:

Book3.xlsx
	A	B	C	D	E
1	Data	To Flag	To CF		Bad Letters
2	One here á	Flag	1		á
3	And à here.	Flag	1		à
4	None in $£"&^*()!@~#		0		ä
5	More than one here ô à	Flag	2		é
6	None here		0		ô
7	And ä here.	Flag	1
8	And é here.	Flag	1
9	And ô here.	Flag	1
10	é	Flag	1
11	None here		0
12	à	Flag	1
Sheet917

Cell Formulas
Range	Formula
B2:B12	B2	=IF(SUMPRODUCT(--ISNUMBER(FIND(E$2:E$6,A2))),"Flag","")
C2:C12	C2	=SUMPRODUCT(--ISNUMBER(FIND(E$2:E$6,A2)))

 

[Toadstool]

Yes, and of course change the cell ranges as appropriate for your data.