Skip to main content
Question

How to Capture Parts of a Long String

  • May 4, 2017
  • 0 replies
  • 162 views
I have a string "FirstName_John_LastName_Smith_Job_Programmer"

I would like to capture with formula text fields

First Name: "John"
Last Name: "Smith"
Job: "Programmer"

I've tried playing around with Part Formula with limited success

Is this possible?
      This topic has been closed for replies.

      • MCFNeil
      • Registered
      • May 4, 2017
      Try this in your formula text field.

      "First Name: "&Part([String], 2, "_")&"<br>
      Last Name: "&Part([String], 4, "_")&"<br>
      Job: "&Part([String], 6, "_")

      This worked when I tested with your example.  Make sure to enable html so you get the line breaks
      • MCFNeil
      • Registered
      • May 4, 2017
      • DanPark1
      • Author
      • Registered
      • May 4, 2017
      Sorry I wasnt clear...
      each section would be its own formula text field.

      First Name would be its own formula text field and i want it to return "John"
      Last Name would be its own formula text field and it would return "Smith"

      I also want the format of the string not to matter.. meaning I could pass in the string 

      "Job_Programmer_LastName_Smith_FirstName_John" and it would return the same results
      ... I suspect that is going to be very difficult and likely impossible with native QuickBase.  You will need a non native script.
      • anomDiebolt
      • Registered
      • May 5, 2017
      >... I suspect that is going to be very difficult and likely impossible with native QuickBase. 

      It is simple:

      Formula for [First Name] =
      If(
        Part([String], 1, "_") = "FirstName", Part([String], 2, "_"),
        Part([String], 3, "_") = "FirstName", Part([String], 4, "_"),
        Part([String], 5, "_") = "FirstName", Part([String], 6, "_")
      )
      Formula for [Last Name] =
      If(
        Part([String], 1, "_") = "LastName", Part([String], 2, "_"),
        Part([String], 3, "_") = "LastName", Part([String], 4, "_"),
        Part([String], 5, "_") = "LastName", Part([String], 6, "_")
      )

      Formula for [Job] =
      If(
        Part([String], 1, "_") = "Job", Part([String], 2, "_"),
        Part([String], 3, "_") = "Job", Part([String], 4, "_"),
        Part([String], 5, "_") = "Job", Part([String], 6, "_")
      )
      Yes, in fact that was simple!  I missed that solution.
      • MCFNeil
      • Registered
      • May 5, 2017
      Oh where is your imagination Mark....


      //Break the individual parts out
      var text PartOne=Part([String], 1, "_")&Part([String], 2, "_");
      var text PartTwo=Part([String], 3, "_")&Part([String], 4, "_");
      var text PartThree=Part([String], 5, "_")&Part([String], 6, "_");
      //Conditional to find the first name in the string
      var text FirstName=If(Contains($PartOne, "First"), "First Name: "&Part([String], 2, "_"), 
      If(Contains($PartTwo, "First"), "First Name: "&Part([String], 4, "_"),
      If(Contains($PartThree, "First"), "First Name: "&Part([String], 6, "_"),
      "No First Name")));
      //Conditional to find the last name in the string
      var text LastName=If(Contains($PartOne, "Last"), "Last Name: "&Part([String], 2, "_"), 
      If(Contains($PartTwo, "Last"), "Last Name: "&Part([String], 4, "_"),
      If(Contains($PartThree, "Last"), "Last Name: "&Part([String], 6, "_"),
      "No Last Name")));
      //Conditional to find the job in the string
      var text Job=If(Contains($PartOne, "Job"), "Job: "&Part([String], 2, "_"), 
      If(Contains($PartTwo, "Job"), "Job: "&Part([String], 4, "_"),
      If(Contains($PartThree, "Job"), "Job: "&Part([String], 6, "_"),
      "No Job")));
      //Combine them into the desired list
      List("<br>", $FirstName, $LastName, $Job)

      It works with any and all combos.  Just as long as the string is divided by the "_" and maintains the format you proposed
      Terms & ConditionsAccessibility statement