Read data from right to left

[mwscott1]

I have a column of data "file name" that I am trying to read each item from right to left starting at the end and stopping at the first - that it comes to and load it as "serial number". I have used sub-stings before but only going from left to right with a set amount of characters to read. In this case not all serial numbers are the same length.

data example

I just want to return the part in red.

-685922-685922-1FTSA07019.txt

-699509-699509-1CTSA05422.txt

-704002-704002-1DTSA10880.txt

[mwscott1]

or really just need what is between the - and the .txt

[#3]

Hi, this can be achieved by using mid function.

= mid('-685922-685922-1FTSA07019.txt', index('-685922-685922-1FTSA07019.txt', '-', -1), (index('-685922-685922-1FTSA07019.txt', '.', -1) - index('-685922-685922-1FTSA07019.txt', '-', -1)) )

Idea is mid (<string>, <index of '-' from right>, <index of '.' from right - index of '-' from right>)

Arun

[MK_QSL · Accepted solution]

SubField(FileName,'-',-1) as Result

Or

SubField(SubField(FileName,'-',-1),'.',1) as Result

[maxgro]

'-' & SubField([file name],'-',-1)