Forum Discussion

waulet's avatar
waulet
Copper Contributor
Aug 02, 2023

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...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Aug 02, 2023

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.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Aug 02, 2023

    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.

Resources