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