Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
SubField(FileName,'-',-1) as Result
Or
SubField(SubField(FileName,'-',-1),'.',1) as Result
or really just need what is between the - and the .txt
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
SubField(FileName,'-',-1) as Result
Or
SubField(SubField(FileName,'-',-1),'.',1) as Result
'-' & SubField([file name],'-',-1)