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 22 days ago
    Edited by Sam Cole 21 days ago
    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 21 days 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
    ------------------------------



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

    Posted 21 days 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
    ------------------------------



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

    Posted 21 days 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
    ------------------------------



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

    Posted 21 days ago
    Thanks, Mark. I'll play around with separate variables. Thanks for the help!

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