Answered

How to extract number from email body using Power Automate and write to Excel?

(0) Share

Report

Posted on by zoey2025

I regularly receive automated alert emails that notify me of ongoing Incident IDs. I need to extract these IDs, but because the email text varies slightly each time, I can't rely on a single, fixed method to capture them all. I was looking for a function that could extract the first occurrence of an N-digit number from the text, but this function doesn't seem to exist in Power Automate. What methods can I use to reliably extract these numbers? My text examples are provided below.

・The following changes have been made to the '[ISK][MDE] 185124:A remote resource was accessed suspiciously incident:

・The following changes have been made to the '（285062）Defender Experts: Multi-stage incident involving Discovery & Command and control on one endpoint' incident:

・The following changes have been made to the '[OKH][MDE] 114985:'Malgent' malware was prevented' incident:

・The following changes have been made to the ' [LAS][0000146534][MDE][incident] Multi-stage incident involving Execution & Defense evasion on one endpoint' incident:

The function I'm currently using can only handle one specific format.

substring(

triggerOutputs()?['body/body'],

add(indexOf(triggerOutputs()?['body/body'], ']'), 2),

sub(

indexOf(

substring(

triggerOutputs()?['body/body'],

add(indexOf(triggerOutputs()?['body/body'], ']'), 2),

':'

)

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (1)

chiaraalina 1,183 on at

Like (0)

Report

Hi

You could use an Office Script, which is often easier for this kind of task. It supports regular expressions (regex), so you can reliably extract the incident number from the email text even when the format changes slightly.

Since you’re already using Excel, you can also have the script add the extracted number directly as a new row in your Excel table.

Use the Run script (Excel) action in Power Automate.

Alternatively, you can use AI Builder to analyze the email body and automatically extract the incident numbers. AI Builder works well if the email text varies a lot and you prefer a no-code approach. This is probably what I would try first.

Let me know if you want to try one of these solutions and if you need help with that!

Was this reply helpful? Yes No
Chriddle 8,443 Super User 2025 Season 2 on at

Like (1)

Report
If the text doesn't contain any other numbers, it's quite simple and efficient.
Take all the number symbols and join them:

Filter array

From:

chunk(outputs('Compose_4'), 1)

Filter:

@isInt(item())

Compose 5

join(body('Filter_array'), '')

Was this reply helpful? Yes No
Verified answer

Chriddle 8,443 Super User 2025 Season 2 on at

Like (3)

Report
If the text may contain other numbers, you can use this to get an array of those numbers:

Select 2

From:

range( 0, sub( length( outputs('Compose_4') ), 1 ) )

Map:

if( isInt(substring(outputs('Compose_4'), item(), 1)), substring(outputs('Compose_4'), item(), 1), ' ' )

Filter array

From:

split( join( body('Select_2'), '' ), ' ' )

Filter:

@not(empty(item()))

Then you have to select the one you want, for example based on the length.

2 people found this reply helpful.

Was this reply helpful? Yes No
zoey2025 15 on at

Like (0)

Report

Thank you so much!!!!!
It works!!

Was this reply helpful? Yes No
Chriddle 8,443 Super User 2025 Season 2 on at

Like (0)

Report

@zoey2025 which one works for you?

Was this reply helpful? Yes No
zoey2025 15 on at

Like (1)

Report

The second one works!!
Thank you so much again!

Was this reply helpful? Yes No