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

[waulet]

Hi,

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!

Thanks 

[#2]

@waulet 

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.

[#3]

alternative option using Excel 365 functions:

=LET(in,$A$2:$A$15,
caps,CHAR(SEQUENCE(1,26,65)),
nums,CHAR(SEQUENCE(1,10,48)),
lowers,CHAR(SEQUENCE(1,26,97)),
other,{"+"},
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.