Jump to content
Dr. Zathras

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

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