CODEWITHSUNDEEP
excelexcel-formula
Peter
Peter

Reputation: 13

How to find cell containing non numerical character, in excel

I have list of 16000 rows. In some rows there might be chinese characters, in that case I need to somehow make rows, that contain these chinese symbols distinctive (Like make them red, put "X" on end of the string, etc)

I was thinking of creating formula, that checks if text only contains letters A-Z and number 0-9. If the condition is not met it add X to the name, or makes the row red... or something to distinguish it easily from the other rows.

I've tried searching the internet

Unfortunately I dont have any code, I dont have any idea how to get to my result

Row like Ronnie Pet 9, will be untouched but row like Ronnie Pet汉字 9, will be red, or the formula will add the X to its name Ronnie Pet汉字 X 9

Thank you for your help

Upvotes: 0

Views: 2766

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34255

Just for interest, I don't see why you couldn't use your original idea of checking that each character was in the list A-Z and 0-9:

=ISERROR(SUM(SEARCH(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1),"0123456789 ABCDEFGHIJKLMNOPQRSTUVWXYZ")))

enter image description here

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

You can use conditional formatting to format the row. One way is to test if the target cell has characters in the CJK Unified Ideographs range.

Depending on how the formula works out, you may need to also test for characters in the range of the various extensions. But you can use the same principal.

Conditional Format Formula (with cell to be tested in column A):

=AND(SUMPRODUCT(N(UNICODE(MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)) >= HEX2DEC("4E00"))),SUMPRODUCT(N(UNICODE(MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)) <= HEX2DEC("9FFF"))))

enter image description here

Upvotes: 1

Related Questions