Formula to find cells with foreign characters

[Timeizmonies]

Hello,

I'm looking for a formula that will locate cells in column B that contain foreign characters like accents and non-English letters.

 

[DRSteele]

This formula will report TRUE when the target cell contains any abnormal characters (i.e,, those not in the list A-Z, a-z, 0-9) and FALSE when it does not contain any abnormal characters. Copy C2 downwards as necessary.

	B	C
ghzdfhbâ
Ag
12Jlp
LoPuYTú
jbhui908nh

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Data[/TD]
[TD="bgcolor: #FFF2CC"]Abnormal Characters[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

</tbody>

Sheet57​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=SUM(--(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)={"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z","0","1","2","3","4","5","6","7","8","9"}))<>LEN(B2)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]

 

[Timeizmonies]

This formula will report TRUE when the target cell contains any abnormal characters (i.e,, those not in the list A-Z, a-z, 0-9) and FALSE when it does not contain any abnormal characters. Copy C2 downwards as necessary.

	B	C
ghzdfhbâ
Ag
12Jlp
LoPuYTú
jbhui908nh

<tbody>
[TD="align: center"]1
[/TD]
[TD="bgcolor: #FFF2CC"]Data
[/TD]
[TD="bgcolor: #FFF2CC"]Abnormal Characters
[/TD]

[TD="align: center"]2
[/TD]

[TD="bgcolor: #E2EFDA, align: right"]TRUE
[/TD]

[TD="align: center"]3
[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE
[/TD]

[TD="align: center"]4
[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE
[/TD]

[TD="align: center"]5
[/TD]

[TD="bgcolor: #E2EFDA, align: right"]TRUE
[/TD]

[TD="align: center"]6
[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE
[/TD]

</tbody>

Sheet57​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2
[/TH]
[TD="align: left"]{=SUM(--(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)={"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z","0","1","2","3","4","5","6","7","8","9"}))<>LEN(B2)}
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]

So that forum attack removed the latest posts on this thread but the formula was picking up spaces and special characters. I added a condition for spaces ," " but it stopped the formula from working.

 

[DRSteele]

Are you sure to invoke with CtrlShft+Enter? And perhaps you could please post the formula you're trying. [Also, it's probably not necessary to Reply with Quotes --- it make the thread too long.]

 

[Timeizmonies]

I am using this formula with Control + Shift + Enter: =SUM(--(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)={"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z","0","1","2","3","4","5","6","7","8","9"," "}))<>LEN(B2)

 

[DRSteele]

Hmmm...works for me on this data:

	B	C
ghzdfhbâ
A g
12Jlp
LoPuYTú
jbhui908nh

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Data[/TD]
[TD="bgcolor: #FFF2CC"]Abnormal Characters[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

</tbody>

Sheet57​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=SUM(--(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)={"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z","0","1","2","3","4","5","6","7","8","9"," "}))<>LEN(B2)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]

 

[Timeizmonies]

[TABLE="width: 236"]
<tbody>[TR]
[TD]This is what I'm getting. These should all be returning false.

Aspen[/TD]
[TD="align: center"]



TRUE[/TD]
[/TR]
[TR]
[TD]Stockholm[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]Astana[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[DRSteele]

Sorry. Hold on...I've got a problem here. The space thing isn't working.

 

[DRSteele]

Let me get this straight. You want to have spaces in the list of acceptable characters, do you not? If so, you need to have " " and not just ""