Discussions

Expand all | Collapse all

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

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

    Posted 12-29-2019 21:18
    Edited by Sam Cole 12-29-2019 23:27
    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
    ------------------------------


  • 2.  RE: Parse out multiple pieces of text from a string and combine them into a new string

    Posted 12-29-2019 23:37
    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
    ------------------------------



  • 3.  RE: Parse out multiple pieces of text from a string and combine them into a new string

    Posted 12-30-2019 06:43
    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
    ------------------------------



  • 4.  RE: Parse out multiple pieces of text from a string and combine them into a new string

    Posted 12-30-2019 09:20

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



  • 5.  RE: Parse out multiple pieces of text from a string and combine them into a new string

    Posted 12-30-2019 18:45
    Thanks, Mark. I'll play around with separate variables. Thanks for the help!

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