The substring function is widely popular and widely used. We have the need all the time to parse the string to get sections of it. Think of, for example, a name where you want to extract the first and last name to store it in the database, or a full URL where you want to extract the domain for later reference.

There are many useful things you can do with this function, but let’s first see how it works.

Usage

It follows a simple pattern.

  1. String
  2. Start index
  3. Length

Notice that we’re not passing the start and end position we want. Instead, we’re indicating that we want to start at index X and get the Y number of characters. This makes things tricky since you cannot randomly put a number in the length if you want “all characters from a certain point.”

Important

If the start index is greater than the number of characters of the string or the start index plus the length is also greater than the number of characters, you’ll get an error.

You have to remember that the count starts at 0, not at one, so if you want the last position, you always have to deduct 1 from the total number of characters.

Example:

Let’s start with a simple example:

String 'Manuel T. Gomes'

substring(variables('String'),0,6)

will return 

'Manuel'

How about if we don’t know the start index. For example, if we want the last name, we want everything after the “T..” To achieve that, we use the indexOf function. But as we mentioned before, we cannot have the sum of the index position and the length to be greater than the string’s size. So we have to do some math using the length function.

Let’s try to get my last name, but first, the start by finding the start of my last name.

String 'Manuel T. Gomes'

indexOf(variables('Test'),'T. ')

will return 

7

Now that we have the position let’s find the length of the full name.

String 'Manuel T. Gomes'

length(variables('Test'))

will return 

14

Now let’s put both together.

String 'Manuel T. Gomes'

substring(variables('Test'),outputs('Index_Of'),sub(outputs('Length'),outputs('Index_Of')))

will return

'T. Gomes'

Let’s break down the function:

  1. First, we provide the full string.
  2. Second, we provide the index of the start of the last name calculated before
  3. Last we have to subtract the length by the start of the name. To do that, we use the sub-function (for reasons that are not important right now, you cannot use them – to do the operation. You have to use a function to do that).

Limitations

I’m not yet aware of the hard limitations of this function. I’ve parsed huge strings and always managed to parse the information that I want. But that doesn’t mean that it will break with a specific number of characters, so be careful when the number of characters is big.

Recommendations:

  1. If you want all characters until the end, you don’t need to perform the calculations as I did above. Since the length parameter is optional and if not provided, the “substring” function takes all the characters beginning from startIndex to the end of the string.
  2. Nest it with care. If you’re parsing a string to extract multiple sections of it, there’s always the temptation to do a nested function to get the correct value. The issue is debuting. If the formula is too complex, you’ll have a bad time when things don’t go your way. I recommend splitting it into multiple actions so that you can see the steps. There isn’t a big performance hit, and it makes debugging a lot easier.
  3. Please note that formulas may have localization-based differences. For example, you should write “substring” with “separating each of the arguments, but if your regional settings are set to Portugal, you should use “;” instead.

Sources:

Microsoft’s substring Function Reference

Back to the Power Automate Function Reference.

 

Photo by Tim Mossholder on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

2 thoughts on “Power Automate: substring Function

Leave a Reply

%d bloggers like this: