74994_en-US

liliconcom

Select rows based on first character

Hi I have a column with a lot of numbers and some text. Below is a snipet of what my data looks like. I'm tring to hide and exclude all rows that aren't 7 digits long and don't start with 5 (i.e. I only want numbers like 5******).

 

5238403
5236577
3238403
ASKU
523660682
asku
2536614
NI
5236610

 

So far I know how to exclude all the rows that aren't 7 characters long, but I can't figure out how to select the rows that don't begin with 5 (e.g. 2536614).

 

dt<

dt<< hide and exclude (1);

Re: Select rows based on first character

If the lot number must be 7 numeric digits, what begins with 5, one more condition needsto be added.

 

dt = current data table();

dt<<SELECT where=""></SELECT>

Also, new column using a formula can find matches using Regex. Column 2 of the table displayed below, uses the formula

 Regex( :Column 1, "(5\d{6}\b)", "\0" ). the selected rows are those selected by the Select Where() command above.

 

Note row 10, 5Z3661A, would not be selected without the additional criteria IsMissing(num(:Column 1)) 

 

Re: Select rows based on first character

For some reason that solution still left a few numbers that didn't fit my criteria, but when I split it up as two separate lines it works great! Thanks!

 

dt <dt <

Re: Select rows based on first character

dt<