TextBetween Function

[Jennell_McIntire]

I was parsing data with a colleague when I learned about the TextBetween function. It is a simple function that can be used in a chart or in the script to return the character(s) that are between two delimiters you specify. Here is the syntax from Qlik Help:

TextBetween(text, delimiter1, delimiter2[, n])

The first parameter, text, is the input string. Delimiter1 is the first character to search for in the text while delimiter2 in the second character to search for in the text. The last parameter, n, is optional and is used to search for a specific delimiter pair. Let’s look at some examples.

The chart expression below returns 1234. The TextBetween function finds the first open parenthesis and the first close parenthesis and returns the text that is between them.

Now if the text included multiple sets of data in parentheses, I can use the chart expression below to get the text in the third parentheses set.

This chart expression returns 3456 because I have set the fourth parameter to 3 in the TextBetween function which means I want the text between the third delimiter pair (the third pair of parentheses).

TextBetween can also be used in the script. In the script snippet below, I load 3 products into a table named Temp. Then I parse the Product field using TextBetween to create another field in the Products table named Product Code.

The result is a table like this:

In the past, I would have approached this parsing exercise differently. I would have used a combination of functions like the index function and the mid function to parse out the product code. Using the TextBetween function is so much easier, cleaner and more straight-forward. I cannot believe it took me this long to learn about it but I am glad I know about it now.

Thanks,

Jennell

[eddiewagt]

Have a look at the subfield function

[Or]

---

@eddiewagtwrote:

Have a look at the subfield function

---

Subfield() can be used to generate similar results to TextBetween() in addition to its more significant usage for splitting data into rows. However, using it as a TextBetween() replacement is often quite inefficient. In the example above, you would need to use something like:

Purgechar(Subfield(Product,'('),')')

or

Left(Subfield(Product,'('),Len(Subfield(Product,'('))-1)

These would be necessary to trim the ending ')' that would be returned by the Subfield() function. You would need to use the latter if the string might have more text after the product number, e.g.

Product A (1234) - Yellow

[christian77]

Good post!