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.
- String
- Start index
- 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:
- First, we provide the full string.
- Second, we provide the index of the start of the last name calculated before
- 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:
- 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.
- 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.
- 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
What if you want the startindex to count from the other side (right-side) of a string?
Hi Reindert,
There’s another function that can help on this. lastIndexOf(, ).
Cheers
Manuel
There actually is a limitation that does not exist with Office functions (Excel) and VBA. If I want to substring from a position in the string value to the end of the string I have to determine the exact number of characters from where I start to the end of the string, or the call will fail. For example, lets say I have a string with a length of 100 characters. In every other Office type product, executing substring(value, 3 ,1000) will return the rest of the string even though 1000 clearly exceeds the actual number of characters available to substring. Using the above function call in Power Automate fails. Power Automate expects this: substring(value,3,97), and being forced to get it exactly right makes the expression overtly complicated.
Hi!
Great catch! I haven’t tested it myself, but it’s a great thing to add here.
Thanks a lot!
In reply to Beyonfthebox. If you want to substring from a position to the end, you just have the provide the startindex position and the substring function will takes all the characters beginning from startIndex to the end of the string
substring(”, )
very clear and good article easy to understand. Thank you