Power Apps text extraction functions made easy

[LaurensM]

While working with strings in Power Apps, you may have come across the requirement to only display a specific part of the text value. These requirements can range from a certain sentence within a paragraph, a specific part of a QR code / unique id, the first occurrence of a certain text value and so on…

In this article we will first familiarize ourselves with the Left, Mid, Right and Find functions – the key building blocks required for text extraction. More importantly, we will explore ways in which we can combine these functions to meet our more complex, dynamic text extraction requirements.

SETTING THE SCENE

Throughout this post we’ll use a fictive QR code as an example. Let’s say we have a warehouse in which each product has a unique QR code. The currently scanned QR code is ‘PX56_Sneakers_2024′ in which the first part makes up the unique id of our product, followed by the product type and year of purchase.

id_productType_purchaseYear

LEFT FUNCTION

The Left function allows us to return a number of characters starting from the beginning of our string:

Left(<String>, <NumberOfCharacters>)

In order to extract the unique Id we could solely use the Left() function:

//Return our unique Id ‘PX56’ - the first 4 characters
Left("PX56_Sneakers_2024", 4)

RIGHT FUNCTION

As you may have guessed already, the Right function does not differ that much from the Left function above. (shocking, I know 😂)

The Right function allows us to extract a certain number of characters starting from the end of our string.

Right(<String>, <NumberOfCharacters>)

Let’s use our previous QR code example, but this time we want the purchase year that is at the end of our string. In this case, let’s use the Right function:

//Return the purchase year ‘2024’ - the last 4 characters
Right("PX56_Sneakers_2024", 4)

MID FUNCTION

The Mid function allows us to return a number of characters starting from a specific position.

Mid(<String>, <StartingPosition>, <NumberOfCharacters>)

Using our QR code as an example, we can leverage this function to fetch the product type:

Mid(
    "PX56_Sneakers_2024",
    //Skip PX56_
    6,
    //Return the product type ‘Sneakers’
    8
)

FIND FUNCTION

The Find function allows you to search for a specific string value within a string, returning the starting position.

Find(<FindString>, <StringValue>)

Optionally, you can provide a starting position as a third parameter. When a starting position is provided, the function will only search for your string value from that position on.

Find(<FindString>, <StringValue>, <StartingPosition>)

Let’s use the Find function to return the index of our first underscore:

//Returns 5
Find("_", "PX56_Sneakers_2024")

DYNAMIC STRING VALUES

The previous code examples all had 1 thing in common: the provided string was static. A lot of scenarios may require you to extract text from a dynamic text value – not allowing us to simply provide a hardcoded <NumberOfCharacters> or <StartingPosition>.

Our QR code ‘PX56_Sneakers_2024’ was just a single example of a possible code value. The structure behind our code is an id consisting out of 2 letters and an incremental number, a dynamic product type and purchase year. Other codes could be ‘FT1702_Gloves_2022’ or ‘GE120_Socks_2023’.

When scanning the QR code we would like the id, product type and year in separate labels regardless of id and product type length. In the examples below we will replace all hardcoded string values with a fictive QR code variable ‘gblQRCode’.

FIND() TO THE RESCUE

Knowing that the length of our QR code can change, we are no longer able to simply hardcode the starting position or number of characters. Luckily the Find function can help us in that regard.

To return the Id we can use a combination of the Left and Find functions. We will return all characters until the first underscore:

//Return unique id
Left(
    gblQRCode,
    //Returns the position of _ - 1
    Find("_", gblQRCode) - 1
)

In order to fetch the product type we can use the Mid and Find functions. We need to keep in mind that (1) the id length and (2) the product type length may vary. In other words, both the starting position and character length are dynamic:

With(
    {
        //Temp store starting position (after first underscore)
        wProdTypeStart: Find("_", gblQRCode) + 1
    },
    Mid(
        gblQRCode,
        wProdTypeStart,
        //Return all characters until the second underscore
        Find("_", gblQRCode, wProdTypeStart) - wProdTypeStart
    )
)

Let’s end with the easiest one, returning the purchase year which can remain hardcoded to 4 characters.

Right(gblQRCode, 4)

Our labels will now always be correctly filled as long as our text values adhere to the expected structure. (id_productType_purchaseYear)

CONCLUSION

Left, Mid, Right are the building blocks for text extraction within Power Apps allowing you to extract specific parts from a string. When using a static string value the individual functions with hardcoded numeric values may suffice, but in dynamic scenarios these functions are commonly paired with the Find function.

If you liked this blog post, feel free to give it a like. 👍 | For more tips and tricks check out my blog (LaurensM). 📘