Filtering a column by a particular character in a specific location

Question

I have a data set with approximately 11,000 rows and I need to isolate only those records with I as the sixth character in the string.

The column header is Alias, and an example of some strings would be:

NUSCKI005673

TFDS-45675

AHKGHI064235

NUSFGS00562

I need to retain only the records NUSCKI005673 and AHKGHI064235 as they have I in the sixth position.  I cannot simply filter by includes NUSCKI and AHKGHI as there are many other permutations with I as the sixth character.

jdunkerley79 · Accepted Answer

An expression of:

Substring([Field1],5,1)='I'

will do what you need

The Substring function is indexed from 0, hence needing 5 not 6.

atcodedog05 · Accepted Answer

Hi @MattR79

Additional to the amazing method @jdunkerley79 suggested you can also use Regex_match. And Regex does a case insensitive match i is same as I (uppercase and lowercase are both captured)

REGEX_Match([Input], ".{5}I.*")

Workflow:

Hope this helps : )