Power Automate: substring Function

The "substring" function is one of those tools we reach for all the time in Power Automate. Whenever we need to pull a piece out of a larger piece of text, like grabbing a first name from a full name, a domain from an email address, or a year from a date code, this is usually the first function that comes to mind.

It is simple on the surface, but it has a couple of behaviors that trip people up, so let's walk through how it works, where it bites, and how to use it well.

Where to find it?

The "substring" function lives in the expression editor, so you won't find it as an action in the connector list. You reach it by clicking inside almost any field, opening the Expression tab, and typing substring. You'll most often use it inside a Compose action, an "Initialize variable" or "Set variable" action, or directly inside a "Condition".

Let's see how to use it.

Usage

The "substring" function follows this pattern.

substring('<text>', <startIndex>, <length>?)

Notice that we are not passing the start and end positions. Instead, we say "start at index X and give me Y characters". That distinction matters, because you cannot just drop a big number into length to mean "everything from here on". If startIndex plus length reaches past the end of the string, the function fails instead of returning what it can.

Two things to keep in your head before we start:

• The count starts at 0, not at 1. So the first character is index 0, and the last character of a string is always at index length - 1.
• If startIndex is greater than the number of characters in the string, or startIndex plus length goes past the end, you get an error.

Basic example

Let's start with a simple one. We want the first name out of a full name.

substring('Manuel T. Gomes', 0, 6)

returns

'Manuel'

We start at index 0 and take 6 characters, which gives us Manuel.

Leaving out the length

If we want everything from a certain point to the end, we can simply omit the length. This is the cleanest way to say "the rest of the string".

substring('Manuel T. Gomes', 10)

returns

'Gomes'

We start at index 10 (the G) and, because we gave no length, we get everything to the end.

Working out the start with "indexOf"

Most of the time we don't know the index up front. Say we want the last name but the first name could be any length. We can find where it starts with the "indexOf" function.

indexOf('Manuel T. Gomes', 'T. ')

returns

7

Now we know the last name section starts at index 7. From here, the easiest approach is to omit the length and grab everything to the end.

substring('Manuel T. Gomes', 7)

returns

'T. Gomes'

Working out the length with "length" and "sub"

Sometimes you genuinely need to compute the length, for example when you want a section in the middle of the string. You can get the total size with the "length" function and do the math with the "sub" function.

length('Manuel T. Gomes')

returns

15

Putting it together, the length we want is the total size minus where we started.

substring('Manuel T. Gomes', 7, sub(15, 7))

returns

'T. Gomes'

Let's break that down.

• 'Manuel T. Gomes' is the full string.
• 7 is the start index of the last name, which we found with "indexOf".
• sub(15, 7) calculates the length (8 characters) by subtracting the start index from the total length. We use the "sub" function because you cannot use a plain minus sign to do arithmetic inside an expression.

Real-world examples

Pulling the domain out of an email address

We want everything after the @. We find the position of the @ with "indexOf", add 1 to skip past it, and then omit the length so we capture the rest.

substring(triggerOutputs()?['body/Email'], add(indexOf(triggerOutputs()?['body/Email'], '@'), 1))

For joana@manueltgomes.com this returns manueltgomes.com. Omitting the length here is important, because email domains vary in length and a hardcoded number would eventually overflow and fail.

Splitting a fixed-width date code

Some systems hand you dates as a solid block of digits like 20260319 (year, month, day). Because the format is fixed-width, "substring" is the perfect tool.

substring(variables('DateCode'), 0, 4)   returns the year  '2026'
substring(variables('DateCode'), 4, 2)   returns the month '03'
substring(variables('DateCode'), 6, 2)   returns the day   '19'

You can then stitch them back together with the "concat" function into something like 2026-03-19. This trick only works when every value is the same length. If your dates use separators like 19/03/2026, reach for the "split" function instead.

Reading a SharePoint lookup value

Lookup and choice columns often arrive in the 123;#112 format, and you only want the value after the #. We find the #, step one character past it, and take the rest.

substring(variables('LookupValue'), add(indexOf(variables('LookupValue'), '#'), 1))

For 123;#112 this returns 112.

Non-intuitive behaviors

A missing delimiter makes "indexOf" return -1, and that breaks "substring"

This is the single most common way "substring" blows up. The "indexOf" and "lastIndexOf" functions return -1 when they don't find what you asked for. If you feed that -1 straight into a "substring" call, you end up with a negative start index or a negative computed length, and the function errors out.

if(equals(indexOf(variables('Text'), '#'), -1), variables('Text'), substring(variables('Text'), add(indexOf(variables('Text'), '#'), 1)))

It does not clamp the way Excel does

If you come from Excel, you might expect MID(value, 3, 1000) behavior, where asking for too many characters just returns whatever is left. The "substring" function does not do that. The moment startIndex plus length passes the end of the string, it throws an error instead of returning what it can. When you want "the rest of the string", omit the length entirely rather than passing a large number.

Negative start index errors, despite what the docs imply

The official documentation suggests that a startIndex below 0 will start from the beginning of the string. In practice, the Power Automate runtime rejects negative start indexes with an error. Don't rely on that documented fallback. If you need to count from the end of the string, use the "slice" function, which actually supports negative indices.

Empty or null input fails

If text resolves to an empty string or null (an empty SharePoint field, a blank Excel cell), "substring" fails because there is no valid index to read. Guard these cases with a "Condition", the "coalesce" function, or an empty() check before the call.

Limitations

It only works on strings

The "substring" function operates on text and nothing else. If you need to pull a slice out of an array, this is not your tool. The "slice" function works on both strings and arrays.

No negative indexing

You cannot count backwards from the end of the string. "substring" only accepts non-negative start indexes. To grab the last few characters of a string, the cleanest option is the "slice" function with a negative start index.

It errors instead of forgiving

Most of the friction with this function comes down to one design choice: it would rather fail than guess. Out-of-range requests, negative numbers, and empty inputs all raise errors rather than returning a best-effort result. That makes your flows predictable, but it also means you have to validate your inputs.

Troubleshooting Common Errors

Symptom: The template language function 'substring' parameters are out of range: 'start index' and 'length' must be non-negative integers and their sum must be no larger than the length of the string. Cause: Either a delimiter wasn't found (so "indexOf" returned -1) or your computed length runs past the end of the string. Solution: Check the delimiter exists before cutting, and prefer omitting the length when you want everything to the end.

if(greater(indexOf(variables('Text'), '@'), -1), substring(variables('Text'), add(indexOf(variables('Text'), '@'), 1)), '')

Symptom: The length of substring can't be longer than 'X' which is the length of the source string. Cause: The length value on its own is larger than the string. This is the classic "I passed 255 to be safe" mistake. Solution: Drop the hardcoded length and let the function run to the end, or clamp the length so it never overruns.

substring(variables('Text'), variables('Start'), min(variables('Wanted'), sub(length(variables('Text')), variables('Start'))))

Symptom: The 'start index' for function 'substring' must be equal to or greater than zero and must be less than 'X' which is the length of the string. Cause: The start index is negative or sits past the end of the string. Seeing '0' as the length usually means the source string was empty. Solution: Validate the input is not empty and the start index is in range before calling the function.

Symptom: InvalidTemplate. Unable to process template language expressions... Cause: This is the wrapper error around the messages above. It also shows up for unrelated typos, like using semicolons instead of commas to separate arguments. Solution: Open the expression and confirm the arguments are separated by commas. Inside Power Automate expressions, the separator is always a comma, regardless of your regional settings.

Recommendations

Here are some things to keep in mind.

Omit the length when you want the rest

If you want everything from a point to the end of the string, just leave out the third argument. You skip all the "length" and "sub" math, and you avoid the most common overflow error in one move.

substring(variables('Text'), variables('Start'))

Guard against a missing delimiter

Any time your start index comes from "indexOf" or "lastIndexOf", remember it can return -1. A short if() check, or a "Condition" action before the cut, will save you from a failed run when the delimiter isn't there.

Pick the right tool for the job

A handy rule of thumb: use "substring" when you know the exact character count, the "slice" function when you know the start and end positions or need to count from the end, and the "split" function when you have a reliable delimiter to break on. Forcing "substring" to do a delimiter job is where most of the pain comes from.

Break complex expressions into steps

It is tempting to nest "indexOf", "length", and "sub" all inside one giant "substring" call. When it works, it is elegant. When it doesn't, it is miserable to debug. Splitting the work across a few "Compose" actions lets you see each intermediate value, and the performance cost is negligible.

Add a comment

If the expression is doing anything clever, add a comment on the action explaining what it extracts and why. Future you, and anyone else opening the flow, will be grateful.

Final Thoughts

The "substring" function is a workhorse for pulling pieces out of text, and most of the time it just works. The two things worth remembering are that it counts from 0 and that it would rather error than guess, so a missing delimiter or an overlong length will stop your flow. Guard your inputs, omit the length when you want the rest, and reach for "slice" or "split" when they fit better, and you'll rarely be caught out.

Sources

• Reference for functions in workflow expressions, Azure Logic Apps and Power Automate (substring)
• Reference for functions in workflow expressions, Azure Logic Apps and Power Automate (slice)

Back to the Power Automate Function Reference

Photo by Tim Mossholder on Unsplash