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
1	Data	Abnormal Characters
2	ghzdfhbâ	TRUE
3	Ag	FALSE
4	12Jlp	FALSE
5	LoPuYTú	TRUE
6	jbhui908nh	FALSE

<tbody>
</tbody>

Sheet57​

Array Formulas Cell Formula C2 {=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)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

 

[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
1	Data	Abnormal Characters
2	ghzdfhbâ	TRUE
3	Ag	FALSE
4	12Jlp	FALSE
5	LoPuYTú	TRUE
6	jbhui908nh	FALSE

<tbody>
</tbody>

Sheet57​

Array Formulas Cell Formula C2 {=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)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

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]

ADVERTISEMENT

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
1	Data	Abnormal Characters
2	ghzdfhbâ	TRUE
3	A g	FALSE
4	12Jlp	FALSE
5	LoPuYTú	TRUE
6	jbhui908nh	FALSE

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet57​

Array Formulas Cell Formula C2 {=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)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

 

[Timeizmonies]

ADVERTISEMENT

This is what I'm getting. These should all be returning false. Aspen	TRUE
Stockholm	TRUE
Astana	TRUE

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[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 ""