Hey everyone!

I am trying to pull out multiple alphanumeric serial numbers out of a giant text field. The serial number could show up in any number of places, but has a fixed format. I need the calculation to pull all occurrences of the serial number and basically put them in a list, to which I will then be able to create a join field from. Honestly, I have no idea where to even start.

The serial number is always going to be 2 Letters and 3 numbers - example: AB123

Source (Text Field):

Big and boisertious rock track, but time selling point, Track is a version of AC101, KD205, GR771. Also check FE454/GG123.

Result:

AC101

KD205

GR771

FE454

GG123

Thanks in advance.

d

2 hours ago, dztrucktion said:

I need the calculation to pull all occurrences of the serial number

Since there can be any number of such occurrences, you will need either a custom recursive function or a looping script to extract them one by one. To install a custom function, you will need the Advanced version of FMP. Which is why I will stop here until you update your profile.

 

 

Edited October 28, 20187 yr by comment

Understood. I have updated my profile to show that I use FileMaker Pro 16 Advanced and I do have some experience with custom functions.

Thank you!

Good. There are several ways to look at this. I think the easiest approach would be to go through the text word-by-word and check if the current word fits the desired pattern of letters and numbers. If it does, send it to the output. Then, if there are more words to check, continue the recursion with the remaining text.

ExtractSerials ( text ) =

Let ( [
countWords = WordCount ( text ) ;
firstWord = LeftWords ( text ; 1 ) ;
mask = Substitute ( firstWord ;  [ "0" ; "#" ] ; [ "1" ; "#" ] ; [ "2" ; "#" ] ; [ "3" ; "#" ] ; [ "4" ; "#" ] ; [ "5" ; "#" ] ; [ "6" ; "#" ] ; [ "7" ; "#" ] ; [ "8" ; "#" ] ; [ "9" ; "#" ] ; 
[ "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" ; "@" ]  )
] ;
If ( mask = "@@###" ; firstWord & ¶ )
&
If ( countWords > 1 ; ExtractSerials ( RightWords ( text ; countWords - 1 ) ) )
)

 

 

Edited October 28, 20187 yr by comment
Oops. It needs to be countWords > 1.

Note: In the example text you have pasted here, there are invisible characters ( ZERO WIDTH NO-BREAK SPACE) surrounding the last period. This will break the suggested process, because the character is not a word delimiter.

I am not sure if this is a forum glitch or a problem with your data caused by copy/pasting. If the latter, you will need to clean it up.

Edited October 28, 20187 yr by comment

10 minutes ago, comment said:

Note: In the example text you have pasted here, there are invisible characters ( ZERO WIDTH NO-BREAK SPACE) surrounding the last period. This will break the suggested process, because the character is not a word delimiter.

This would explain the removal of any value with a period directly after it. I would assume it was from my imported data. I fixed it by using the replace command to refresh the fields.

47 minutes ago, comment said:

ExtractSerials ( text ) =

Worked like a charm thank you so much. I did have to include a Substitute line in the calculation field to remove an extra carriage return, but this solution was a stoke of genius! Thank you!

 

1 minute ago, dztrucktion said:

I did have to include a Substitute line in the calculation field to remove an extra carriage return

You said you wanted to use this as a multi-key match field, so the extra carriage return should pose no problem. Several Filemaker's native functions leave a trailing carriage return, e.g. LeftValues(). 

26 minutes ago, comment said:

You said you wanted to use this as a multi-key match field, so the extra carriage return should pose no problem. Several Filemaker's native functions leave a trailing carriage return, e.g. LeftValues(). 

Just a little bit of an OCD thing for me, Thanks again!