List numbers containing everything but A to Z, 0 to 9 and "+"

Copper Contributor



I have a large list of GTIN numbers in column A. Only the characters A- Z, 0-9 and "+" are allowed. In column B i want to list all the numbers that contain anything but that.


I know there has to be a solution using IF, Search, ISTEXT etc. but i cannot seem to make it work.


Thanks for your help!



2 Replies


The attached demo workbook contains a custom VBA function that uses regular expressions.

You'll have to allow macros when you open the workbook after downloading it.

alternative option using Excel 365 functions:

exclude, HSTACK(caps,nums,other),
found, BYROW(in,LAMBDA(entry,LEN(CONCAT(TEXTSPLIT(entry,exclude)))>0)),
FILTER(in,found,"none found"))

note that line 6 is where you can decide what character sets to exclude. In this case I didn't include the lower case character set.