Highlight cells with text in different languages

[Lux Aeterna]

Hello everyone!

I've got an excel file that we add names in both Greek and Latin characters. Some of these characters are common in both languages and, sometimes, words are carelessly written half in English half in Greek. This is a problem when we try to find a name in our file.

Is it possible to highlight cells, preferably using conditional formatting, that are written in two different languages?

Thank you in advance!

 

[Fluff]

How about with conditional formatting

Fluff.xlsm
	N
1
2	ΕΧCEL IS AMAZING
3	EXCEL IS AMAZING
4	ΕΧΨΕΛ ΙΣ ΑΜΑΖΙΝΓ
5	ΕΧΨΕΛ ΙΣ ΑΜΑΖΙΝΓ
6
7
8
9
10
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N2:N8	Expression	=SUM(--(UNICODE(MID(N2,ROW(INDIRECT("1:"&LEN(N2))),1))>122))>0	text	NO

 

[Lux Aeterna]

How about with conditional formatting

Fluff.xlsm
	N
1
2	ΕΧCEL IS AMAZING
3	EXCEL IS AMAZING
4	ΕΧΨΕΛ ΙΣ ΑΜΑΖΙΝΓ
5	ΕΧΨΕΛ ΙΣ ΑΜΑΖΙΝΓ
6
7
8
9
10
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N2:N8	Expression	=SUM(--(UNICODE(MID(N2,ROW(INDIRECT("1:"&LEN(N2))),1))>122))>0	text	NO

I substituted N2 with C5 as this is the first cell of my data (it starts fron C5 and ends in C100000). Is that ok? It works for mixed languages, but it it identifies cells that are written only in greek too.

 

[Fluff]

Ok, how about

=SUM(--(UNICODE(MID(SUBSTITUTE(C5," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(C5," ","")))),1))>122))<>LEN(SUBSTITUTE(C5," ",""))

 

[Lux Aeterna]

Ok, how about

=SUM(--(UNICODE(MID(SUBSTITUTE(C5," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(C5," ","")))),1))>122))<>LEN(SUBSTITUTE(C5," ",""))

Now it identifies english characters too. Greek and mixed work fine.

 

[Fluff]

Ok, how about

=AND(SUM(--(UNICODE(MID(SUBSTITUTE(C3," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(C3," ","")))),1))>122))<>LEN(SUBSTITUTE(C3," ","")),SUM(--(UNICODE(MID(SUBSTITUTE(C3," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(C3," ","")))),1))>122))>0)

 

[Lux Aeterna]

=AND(SUM(--(UNICODE(MID(SUBSTITUTE(C3," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(C3," ","")))),1))>122))<>LEN(SUBSTITUTE(C3," ","")),SUM(--(UNICODE(MID(SUBSTITUTE(C3," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(C3," ","")))),1))>122))>0)

It seems to be working perfectly! I only had to change C3 to C5!

Thanks a lot!

@Anthony47 thank you for your help too!

 

[Fluff]

Glad we could help & thanks for the feedback.