Skip to main content
Question

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

  • December 30, 2019
  • 0 replies
  • 198 views
Forum|alt.badge.img+4
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
------------------------------
This topic has been closed for replies.

Forum|alt.badge.img+4
  • SamCole
  • Author
  • Registered
  • December 30, 2019
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
------------------------------
    Forum|alt.badge.img+15
    • DonLarson
    • Registered
    • December 30, 2019
    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
    ------------------------------
    MarkShnierYou
    Forum|alt.badge.img+24

    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
    ------------------------------
    Mark Shnier. Your Quickbase Coach
    Terms & ConditionsAccessibility statement