Forum Discussion
List numbers containing everything but A to Z, 0 to 9 and "+"
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...
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:
=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.