# Formula to find cells with foreign characters

#### Timeizmonies

##### New Member
Hello,

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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.

BC
1DataAbnormal Characters
2ghzdfhbâTRUE
3AgFALSE
412JlpFALSE
5LoPuYTúTRUE
6jbhui908nhFALSE

<tbody>
</tbody>
Sheet57

Array Formulas
CellFormula
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>

Last edited:
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.

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.]

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)

Hmmm...works for me on this data:

BC
1DataAbnormal Characters
2ghzdfhbâTRUE
3A gFALSE
412JlpFALSE
5LoPuYTúTRUE
6jbhui908nhFALSE

</tbody>
Sheet57

Array Formulas
CellFormula
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>
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>

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

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

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

Last edited:
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 ""

Replies
1
Views
171
Replies
4
Views
312
Replies
3
Views
190
Replies
3
Views
88
Replies
12
Views
328

1,196,357
Messages
6,014,768
Members
441,847
Latest member

### 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.

### Which adblocker are you using?

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

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