Forum Discussion

SamCole's avatar
SamCole
Qrew Cadet
6 years ago

Parse out multiple pieces of text from a string and combine them into a new string

I have a field that is a text string of item serial numbers for a given project, and are comma separated. Typically the value of the field is something like this for each project:

"ULT123, 512001, 512002, ULT456, ULT789, 412001"
(the string will be of varying lengths and not in alpha/numerical order. Could have one serial #, could have several.)

I am trying to create a new formula field which will pull out only the serial numbers that begin with "ULT" (and keep them comma separated, if possible). Would like the formula field to generate the following string from the original string above:

"ULT123, ULT456, ULT789"

Can't seem to figure out how to use the Part function to ​pull out each instance of a serial beginning with "ULT", or do I need to use variables?

------------------------------
Sam Cole
------------------------------
Formulas and functions
  • SamCole's avatar
    SamCole
    Qrew Cadet
    6 years ago
    Played around with some ideas from other discussions, and came up with this:

    List(", ",
    If(Begins(Part([SN_List], 2, "U"), "L"), "U" & Left(Part([SN_List], 2, "U"), 5)),
    If(Begins(Part([SN_List], 3, "U"), "L"), "U" & Left(Part([SN_List], 3, "U"), 5)),
    If(Begins(Part([SN_List], 4, "U"), "L"), "U" & Left(Part([SN_List], 4, "U"), 5)),
    If(Begins(Part([SN_List], 5, "U"), "L"), "U" & Left(Part([SN_List], 5, "U"), 5)),
    If(Begins(Part([SN_List], 6, "U"), "L"), "U" & Left(Part([SN_List], 6, "U"), 5)),
    If(Begins(Part([SN_List], 7, "U"), "L"), "U" & Left(Part([SN_List], 7, "U"), 5)),
    If(Begins(Part([SN_List], 8, "U"), "L"), "U" & Left(Part([SN_List], 8, "U"), 5))
    ))

    It returns exactly what I'm looking for from the original post. But this formula assumes:
    1) There will not be more than a set number of serial #s, or I'd have to add more If statements
    2) The serial #s that i'm parsing will always be the same length of 6 characters ("U" plus the 5 characters after it)

    wondering if there's a more elegant way that doesn't rely on those assumptions.​​.

    ------------------------------
    Sam Cole
    ------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    6 years ago
    Sam,

    Where is the data coming from?   Is it imported by a script or entered by Users? 

    The answer might be to change the original entry process and turn these into individual records instead of a text string.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago

    I think you were on the right track but note that you can use the, as of the limiter to separate out the parts.

    You could gather up the comma separated Parts in separate variables. 


    Then list then together with IFs to check if they begin with ULT. That way the length won't matter.  



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • SamCole's avatar
      SamCole
      Qrew Cadet
      6 years ago
      Thanks, Mark. I'll play around with separate variables. Thanks for the help!

      ------------------------------
      Sam Cole
      ------------------------------