Can I extract specific text from the middle of a string?

Hi Bob,

Let's say your field was called "Description".

If you use this formula:

If(Contains([Description],"S5"),
"S5"&Left(Right([Description],"S5"),3),"")


It will find the first instance of "S5" in your field then combine S5 with the first 3 characters after S5 in the string of text. It is sort of a quick and dirty solution, but I think it should probably work. If S5 is not detected, the formula text field will remain blank.

Let me know if that works for you. Good Luck!

Eric

Hey Eric,

Thanks for the suggestion. Clever use of Right and Left together like that - I was stuck on using Mid.

The formula you provided works sometimes, but not all the time. It looks like the formula starts backwards at the end of the string and then pulls text whenever it comes across /either/ a "S" or a "5" rather than the two together. For example, here's part of a sentence I have:

"S5775: To return blah blah blah blah
Should use 0, 2, 5, or 8"

The formula outputs: "R4, o" so it seems the whole string "S5" in the left/right part of the formula isn't being respected and it is simply pulling everything after it finds "5". The same thing occurs with a sentence that contains a capital "S".

Looks like this is due to the way Left and Right work - per the documentation:
"The second argument, d, is a text value containing all the possible delimiter characters."

So it seems each character put in the formula acts as a delimiter, rather than the whole string. Is it possible for a string to act as a delimiter rather than the individual characters?

This should work. Admittedly some brute force involved here, but the formula does have some repetitive elegance to  it at least.

var text SOne   = [Pickup Description];

var text STwo   = NotLeft($SOne,"S");

var text SThree = NotLeft($STwo,"S");

var text SFour   = NotLeft($SThree,"S");

var text SFive    = NotLeft($SFour,"S");

var text SSix     = NotLeft($SFive,"S");

var text SSeven= NotLeft($SSix,"S");

var text SEight   = NotLeft($SSeven,"S");

var text SNine= NotLeft($SEight,"S");

var text STen   = NotLeft($SNine,"S");



If(

Begins($SOne,"5"), "S" & Left($SOne,4),

Begins($STwo,"5"), "S" & Left($STwo,4),

Begins($SThree,"5"), "S" & Left($SThree,4),

Begins($SFour,"5"), "S" & Left($SFour,4),

Begins($SFive,"5"), "S" & Left($SFive,4),

Begins($SSix,"5"), "S" & Left($SSix,4),

Begins($SSeven,"5"), "S" & Left($SSeven,4),

Begins($SEight,"5"), "S" & Left($SEight,4),

Begins($SNine,"5"), "S" & Left($SNine,4),

Begins($STen,"5"), "S" & Left($STen,4))

>The formula you provided works sometimes, but not all the time. 

I have made this same mistake thinking that Right() and Left() will parse on multi-character strings rather than any one of the delimiter characters. This is a common mistake to programmers because most languages support this trivial text parsing feature as well as more advanced string manipulation methods and regular expressions.

I hesitate to provide this answer because this is trivial to do in JavaScript and the native QuickBase solution is a hack that is both cumbersome, promotes bad practices and has an upper limit to the number of tokens it could parse out.

But here is my solution that will attempt to parse the first seven S5* tokens:

List("\n",
  If(Begins(Part(, 2, "S"), "5"), "S" & Left(Part(, 2, "S"), 4)),
  If(Begins(Part(, 3, "S"), "5"), "S" & Left(Part(, 3, "S"), 4)),
  If(Begins(Part(, 4, "S"), "5"), "S" & Left(Part(, 4, "S"), 4)),
  If(Begins(Part(, 5, "S"), "5"), "S" & Left(Part(, 5, "S"), 4)),
  If(Begins(Part(, 6, "S"), "5"), "S" & Left(Part(, 6, "S"), 4)),
  If(Begins(Part(, 7, "S"), "5"), "S" & Left(Part(, 7, "S"), 4))
)

You can increase the number of tokens it will attempt to parse by adding additional lines using the obvious pattern of statements. You can use this text as a test case to see what I mean:

I HAVE SASSAFRAS AND BOXES OF S5442, S5443, S5444, S5445, S5446, S5447, S5448, S5449, S5450, S5451, S5452


FWIW, if you want a JavaScript solution you can adopt what is presented in this Q&A:

https://quickbase-community.intuit.com/questions/1193611-how-to-globally-replace-string-in-text-fiel...

Dan's suggestion is more elegant than mine in that it is shorter and also it will parse out multiple Part numbers, where mine will stop at the first Part # finds.

Thanks for the plug but the problem with this solution is that it is so inflexible. The first thing that is going to happen is a user is going to enter s5442 instead of S5442 and the formula will have to be patched.

well, then this would work

List("\n",
  If(Begins(Part(, 2, "S"), "5"), "S" & Left(Part(, 2, "Ss"), 4)),
  If(Begins(Part(, 3, "S"), "5"), "S" & Left(Part(, 3, "Ss"), 4)),
  If(Begins(Part(, 4, "S"), "5"), "S" & Left(Part(, 4, "Ss"), 4)),
  If(Begins(Part(, 5, "S"), "5"), "S" & Left(Part(, 5, "Ss"), 4)),
  If(Begins(Part(, 6, "S"), "5"), "S" & Left(Part(, 6, "Ss"), 4)),
  If(Begins(Part(, 7, "S"), "5"), "S" & Left(Part(, 7, "Ss"), 4))

No it won't as you didn't include the lower case "s" among the delimiters of the Part() calls within the Begins(). But the bigger issue is that you will always be debugging and wrestling with the limitations of the formula language. In JavaScript you can parse any pattern whatsoever using regular expressions and compute any output you desire - effortlessly. One short regular expression /S5\d{3}/g will parse out all the S5* tokens:

var f = "I HAVE SASSAFRAS AND BOXES OF S5442, S5443, S5444, S5445, S5446, S5447, S5448, S5449, S5450, S5451, S5452";

f.match(/S5\d{3}/g).join("\n");

result:

"S5442
S5443
S5444
S5445
S5446
S5447
S5448
S5449
S5450
S5451
S5452"

yes, quite right so this is now the best native solution

List("\n",
  If(Begins(Part(, 2, "Ss"), "5"), "S" & Left(Part(, 2, "Ss"), 4)),
  If(Begins(Part(, 3, "Ss"), "5"), "S" & Left(Part(, 3, "Ss"), 4)),
  If(Begins(Part(, 4, "Ss"), "5"), "S" & Left(Part(, 4, "Ss"), 4)),
  If(Begins(Part(, 5, "Ss"), "5"), "S" & Left(Part(, 5, "Ss"), 4)),
  If(Begins(Part(, 6, "Ss"), "5"), "S" & Left(Part(, 6, "Ss"), 4)),
  If(Begins(Part(, 7, "Ss"), "5"), "S" & Left(Part(, 7, "Ss"), 4))

I do agree that the one line of code does look powerful

But the "best" known QuickBsae formula fails on this test case:

I am driving down US54 to pick up a load of SASSAFRAS AND BOXES OF S5442

You simply cannot get the anywhere near the level of precision with the extremely limited set of text manipulation functions in QuickBase compared to using script and regular expressions.

Thanks a bunch guys - I realize it's not the most elegant solution when compared to script/code, but this is pretty much what I was looking for and it works.

B> ...  it's not the most elegant solution ...

Southbound US54 closed due to overturned truck spilling SASSAFRAS AND BOXES OF S5442, S5443, S5444, S5445, S5446, S5447, S5448, S5449, S5450, S5451, S5452 - Northbound US54 carrying <script> traffic unaffected.

Hey Dan,

You simply cannot get the anywhere near the level of precision with the extremely limited set of text manipulation functions in QuickBase compared to using script and regular expressions

You mentioned that this could be done with script and regular expressions - can this be done within a quickbase formula field?

I've noticed that within a text formula field I can include "javascript($string..." and it will accept it, but any parameters of the javascript variable willl error out (ex: javascript($string.search("S5")) will result in an error at the .search part).