Filtering a column by a particular character in a specific location

[MattR79]

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 solution]

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 solution]

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 : )

[MattR79]

Thank you both for the solutions.  I tried them and got exactly what I needed.