Jump to content
Colin Hunter

Delete the field contents before the ocurrence of any two letters

Recommended Posts

I need to be able to standardize the contents of a field to just the characters after two letters, including the two letters. It's easier to give a few examples rather than describe the calculation:

  • R01AR064810-01A1 -> AR064810-01A1
  • AR064810-01A1 -> AR064810-01A1
  • DA035813 -> DA035813
  • F32NS084680-01A1 -> NS084680-01A1
  • 14SHSP603-01 -> SHSP603-01
  • T32AI007524 -> AI007524

In other words, the calculation needs to examine the text string, find the location of two letters next to each other (there will only be a single occurrence of two letters next to each other in the string), then delete everything to the left of the two letters.

Is such a calculation possible in FMP 12 or 13?

Thanks

Share this post


Link to post
Share on other sites

How about:

Let ( [
mask = Substitute ( YourField ;  [ "A" ; "@" ] ; [ "B" ; "@" ] ; [ "C" ; "@" ] ; [ "D" ; "@" ] ; [ "E" ; "@" ] ; [ "F" ; "@" ] ; [ "G" ; "@" ] ; [ "H" ; "@" ] ; [ "I" ; "@" ] ; [ "J" ; "@" ] ; [ "K" ; "@" ] ; [ "L" ; "@" ] ; [ "M" ; "@" ] ; [ "N" ; "@" ] ; [ "O" ; "@" ] ; [ "P" ; "@" ] ; [ "Q" ; "@" ] ; [ "R" ; "@" ] ; [ "S" ; "@" ] ; [ "T" ; "@" ] ; [ "U" ; "@" ] ; [ "V" ; "@" ] ; [ "W" ; "@" ] ; [ "X" ; "@" ] ; [ "Y" ; "@" ] ; [ "Z" ; "@" ]  ) ;
pos = Position ( mask ; "@@" ; 1 ; 1 )
] ;
Right ( YourField ; Length ( YourField ) - pos + 1  ) 
)

Note that this assumes a "letter" is one of the 26 upper-case letters of the English alphabet.

Edited by comment
  • Like 1

Share this post


Link to post
Share on other sites

That's perfect, and lightning fast to boot. If we ever meet I hope you'll allow me to buy you a beer or whatever liquid refreshment is your preference in thanks.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Who Viewed the Topic

    1 member has viewed this topic:
    hutchlad 
×

Important Information

By using this site, you agree to our Terms of Use.