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 has to be a solution using IF, Search, ISTEXT etc. but i cannot seem to make it work.

 

Thanks for your help!

 

Thanks 

Formulas and Functions

2 Replies

  • 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