Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Repeating Alphanumeric Strings


dztrucktion
 Share

Recommended Posts

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.

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

Link to comment
Share on other sites

This is a toughie.  Will the text field have any other numbers in it?

Something like:

"Their 3rd big and boisertious rock track, but time selling point, Track is a version of AC101, KD205, GR771. Also check FE454/GG123".

Link to comment
Share on other sites

My initial feeling is if you use this expression (doesn't have all the separators, so you'll mileage may vary:

Substitute(List(YOURTABLE::YourField);[" ";"¶"];["/";"¶"];[".";""];[",";""])

You get this result.

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

Now the big question (someone smarter than me needed), how to find the values you want, or filter out the ones you don't. 

There may be a custom function for this, but I couldn't find it.

Link to comment
Share on other sites

Steve thank you so much. I guess that now that I have it in a list, I could filter the list against the tracks themselves and at least get a cleaner version of list. I really appreciate your suggestion

Link to comment
Share on other sites

If no one answered by Monday I was going to suggest you try your question on the other forum, because this is probably child's play for Comment.  However I see you got the answer.  You should post that answer (giving proper credit of course) on this forum too, or post a link to the thread.

Link to comment
Share on other sites

Thanks to comment, this was answered.

Thanks again for everyone's help!

 

Quote

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 ) ) )
)

 

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use