How to Capture Parts of a Long String

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
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?
Photo of Dan Park

Dan Park

  • 410 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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
(Edited)
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Photo of Dan Park

Dan Park

  • 410 Points 250 badge 2x thumb
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
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
... I suspect that is going to be very difficult and likely impossible with native QuickBase.  You will need a non native script.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,372 Points 20k badge 2x thumb
>... 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, "_")
)
(Edited)
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
We were both working on this at the same time. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
Yes, in fact that was simple!  I missed that solution.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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