Power Automate: lastIndexOf Function

The "lastIndexOf" function in Power Automate is the counterpart of the "indexOf" function, but it returns the last occurrence. It always returns a number, and it will return:

1. Zero or greater for the position of the search text
2. -1 if the text is not found.

Please note that the position starts at zero, not one, so the first character is at position 0. This is common for programmers, but it can be confusing for those who are not.

Usage

It follows a simple pattern.

lastIndexOf('<text>', '<searchText>')

Let's see some examples. First, let's set up our test environment by creating an instant flow with two parameters. This way, we can trigger the Flow with the parameters without changing it.

The formula will be as follows:

lastIndexOf(triggerBody()?['text'],triggerBody()?['text_1'])

Example:

Let's start with a simple example:

String 'Manuel T. Gomes'
Search 'T.'

will return

7

Notice that you can search for one or multiple characters. The search is case insensitive, meaning that "gOmeS" or "GoMES" are the same.

String 'Manuel T. Gomes'
Search 'Teixeira'

will return

-1

The function can't find the string, so it returns -1.

Finally, let's add multiple instances of the string and see what we get.

String 'Manuel T. Gomes where the T. is the last occurrence that I want to get'
Search 'T.'

will return

26

Real-world examples

Getting a file extension

File names can have more than one dot, such as "quarterly-report.final.xlsx". The "indexOf" function would stop at the first dot, but "lastIndexOf" finds the one that starts the extension. Combine it with the "add" function and the "substring" function:

substring(variables('fileName'), add(lastIndexOf(variables('fileName'), '.'), 1))

For "quarterly-report.final.xlsx", the last dot is at position 22. We add one to skip the dot, and the "substring" function returns "xlsx".

If the name has no dot, "lastIndexOf" returns -1, the "add" function turns it into 0, and you get the whole file name back. Add a "Condition" action to check for -1 first if that matters to you.

Edge Cases

Empty values

The function behaves differently when one of the values is empty:

• If only the text is empty, it returns -1.
• If both the text and the search string are empty, it returns 0.
• If only the search string is empty, it returns the length of the text minus 1.

For example, the text "hello world hello world" has 23 characters, so an empty search string returns 22. If your values come from a user or another system, check that they are not empty before you call the function.

Limitations

I could not find a hard limitation for this function. I've passed strings with 30000 characters, and it returned almost instantly. But keep in mind that an undocumented limit may exist, so keep your strings as short as possible.

Recommendations

Here are some things to keep in mind.

Keep the parameters in variables

Don't pass them directly in the formula. You can do that, but it will make your formula quite prone to error. For example, if you want to check the string "Manuel' T. Gomes'" (Notice the quotes), the function will return an error because it will consider the quotes as the end of the string. Having data on separate variables helps a lot in avoiding these issues.

Don't nest

There's no real reason to do it, but if you find yourself with nested "lastIndexOf" functions in a formula, you should review it and make everything more straightforward.

Localization

Please note that formulas may have localization-based differences. For example, you separate the arguments of the "lastIndexOf" function with a comma, but if your regional settings are set to Portugal, you should use a semicolon instead.

Final Thoughts

The "lastIndexOf" function is a quick way to find where the last match sits in a text. Remember that counting starts at zero and that -1 means "not found". Keep your values in variables, and it will behave the same every time.

Sources

• Microsoft's lastIndexOf Function Reference

Back to the Power Automate Function Reference

Photo by David Clode on Unsplash