“I receive an email with a ton of information that I’d like to proces automatically, how can I extract the relevant text using Power Automate?”
There’re many situations when you have more information than you need. It can be a long email subject, file name with details on the file, or a whole structured email, all of them containing one piece of text that’s important for you. How do you extract it? What possibilities does Power Automate offer?
Slice(…)
The first expression you could use is slice(…). Slice expects 3 parameters – the whole string, the start position, and the end position.
slice(<string>, <startPosition - included>, <endPosition - excluded>)For example, if I have an email subject “Incident number [#1324]”, I can get the incident number with the expression below.
slice('Incident number [#1324]', 18, 22)But you probably don’t know the exact location of the text, which is why this expression is often combined with indexOf(…). IndexOf(…) expects 2 parameters, the string and the character you’re looking for, and it’ll return their position. E.g. to find the position of the #.
indexOf('Incident number [#1324]', '#') = 17If you combine it in a single expression with the slice(…) while searching for the ] as the end character…
slice('Incident number [#1324]', indexOf('Incident number [#1324]', '#'), indexOf('Incident number [#1324]', ']'))…the result will be #1234.
Since the character on the start position is included, you must skip it by adding 1 to the start index.
slice('Incident number [#1324]', add(indexOf('Incident number [#1324]', '#'),1), indexOf('Incident number [#1324]', ']'))
Substring(…)
Another expression you could use is substring(…). It’s similar to slice(…) with a difference in the 3rd parameter. While slice(…) expects the end position, substring(…) needs the number of characters to take.
substring(<string>, <startPosition - included>, <numberOfCharacters>)To get the incident number from the example above, it’d look as below.
substring('Incident number [#1324]', 18, 4)Substring(…) is a good expression if you know how long is the text you need. If it’s an ID that’s always 4 characters long, you can use substring(…) with a single indexOf(…).
substring('Incident number [#1324]', add(indexOf('Incident number [#1324]', '#'),1), 4)
But if the text length can vary you’re better off with slice(…) or the last expression – split(…).
Split(…)
Split(…) is my favourite expression when extracting a piece of string. With split(…) you’re not extracting text directly, but you split the string into smaller pieces which are easier to navigate. Split(…) has two parameters – the string a the character to split by.
split(<string>, <character>)Following on the example, you can split the string by the # character.
split('Incident number [#1324]', '#')The result will be an array as below, which you can then easily navigate using indexes.
[
"Incident number [",
"1324]"
]The number is the second row, that’s index 1.
split('Incident number [#1324]', '#')[1]Remove the closing ] with the replace expression and you’re done.
replace(split('Incident number [#1324]', '#')[1],']','')
Summary
Power Automate gives you three ways to extract a piece of text from a longer string. If you’re looking for a text with fixed length, the best approach might be the substring(…) expression. But if the text length isn’t fixed, it’s much better to use slice(…) or split(…), with my preference being the latter.
If you’d like to learn more on the other expressions, you might check the cheat sheet I created.
If I wanted to extract the first character of each name, how’d I go about that?
I tried with no joy:
substring(variables(‘StudentFirstMidName’), add(indexOf(variables(‘StudentFirstMidName’), ‘ ‘), 1), 1)
So for example, Montague Middles Names Miller, I would want MMNM. My first and middle name is one MS Form entry and last name is another (no issues extracting that with substring(variables(‘StudentFirstMidName’), 0, 1)
Any insight is greatly appreciated!
While I’m not an expert, far from it, I would recommend using split function by the ‘ ‘ character. That would create a list with 3 strings, first, middle and last name.
Then you can just do a substring on each for the first character and combine them with concat fucntion.
It might looks something like this:
concat(
substring(split(variables(‘StudentFirstMidName’), ‘ ‘)[0], 0 ,1), substring(split(variables(‘StudentFirstMidName’), ‘ ‘)[1], 0 ,1), substring(split(variables(‘StudentFirstMidName’), ‘ ‘)[2], 0 ,1)
)
Hi Petar, thank you for getting back and apologies for my late response, I didn’t get a notification email or my poor personal inbox management got me.
In the end I did this:
Select
From: split(trim(variables(‘StudentFirstMidName’)),’ ‘)
Map mode: substring(item(),0,1)
and this achieved what I wanted. I placed the output for Select and another compose I did for the last name next to each other.
Hello Selom,
you can split it by ‘ ‘, take() 1 character from each name, and join them back, e.g.
concat(take(split(‘Tom Riha’, ‘ ‘)[0],1), take(split(‘Tom Riha’, ‘ ‘)[1],1))
Hi Tom, Apologies for my late response.
In the end I did this:
Select
From: split(trim(variables(‘StudentFirstMidName’)),’ ‘)
Map mode: substring(item(),0,1)
and this achieved what I wanted. I placed the output for Select and another compose I did for the last name next to each other.
ah sorry I did some more compose for the Select with a Join.
How can I get this to work if the “incident number” is sometimes 4 or sometimes 5 characters? It doesn’t seem like the expressions care about the count of characters within the square brackets but I can’t seem to figure it out 😉
For my purposes I am looking for text ‘Service Provider: ‘ where it could be either ‘Service Provider: abcdef’ or ‘Service Provider: xyz’
Thx
Hi,
Thank you for this great post. It is really good detail.
My question is about trying to extract two values from the subject line of an email.
[app name] [ app status]
How to I get both of those values from within the brackets. Thank you!
How can i get a certain portion of the email subject?but the subject is always changing
when i am extracting afrom .txt document, the slice is not reading line breaks , so the output is single continous paragraph.
How are these going to work when there are repeating patterns in a string? Knowing the exact ending index positions is impossible.
“teams(‘teamId’)/channels(‘channelId’)/messages(‘messageId’)/replies(‘replyId’)”
Hello Aaron,
it won’t. If there’s nothing unique in the string you can catch on then these expressions won’t help you.
Hello,
what do I do, if I know the string length but just the beginning of the string is constant but the rest of it is dynamic in every email?
Thank you for your help.
So long,
Toni
Hello Toni,
you always need something to catch on, some word, position, character, something that’ll help you identify the right place.