A person with a puzzled and frustrated expression, attempting to understand foreign language text on a screen or document

Published in: Google Ads

Excel formula to find non-English or foreign characters – Google ads

Author Raph Sebbag

Published on: November 4, 2024

For many Google ad experts out there, you know that non english characters cause errors when adding negative keywords to a campaign. When you are working with a small list of keywords then its easy to manage. But when you a list of hundreds or thousands of negative keywords it can be very painstaking to find the source of the bulk upload errors.

I recently felt frustrated with this monotonous, arduous task when I was sifting through thousands of unwanted search terms fo national e-commerce clients Google shopping ads campaign.

So I went to Google and did a search. I was presented with a range of results, which I had to sift through, mainly, from chat forums. So, as many of us do nowadays, I went to my ChatGPT space, and was given the perfect solution in seconds in the first go.

So if you want to save yourself time and become more of a negative keyword pro too, then copy the formula below (thanks to ChatGPT of course – credit where its due).

To identify non-English characters in Excel, you can use the following formula in conjunction with the UNICODE function. This formula checks for any character that does not fall within the standard ASCII range (0-127), which typically covers English characters and basic symbols.

Here’s an example formula you can use:

=IF(SUMPRODUCT(--(UNICODE(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))>127))>0, "Non-English", "English")

How it Works:

MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1): Breaks down each character in cell A1.
UNICODE(...): Retrieves the Unicode value for each character.
>127: Checks if any character has a Unicode value greater than 127.
SUMPRODUCT(...)>0: Sums up the number of characters with Unicode values greater than 127. If there are any, it labels the cell as “Non-English.”

Instructions:

Replace A1 with the cell you want to check.
Copy the formula into the desired cell.

This formula will display “Non-English” if any character in the cell is outside the English ASCII range, or “English” if all characters fall within it.

PS: can you guess how I got the feature image for this blog 😎

Raph Sebbag

With 15+ years of PPC & SEO marketing experience, Raph is obsessed with delivering tangible and life-changing results for his clients, often generating 8x or more ROI. On the home front, he's a dad to five great kids, but his amazing wife would argue there’s a sixth kid in the mix (guess who that is!) - to help around the house, he gets out of everyone's way through therapeutic mountain biking!
View all posts