extracting/parsing a specific string from unstructured email

(0) Share

Report

Posted on by AY-13021440-0

Hi community,

I need assistance regarding the following issue:

If i need to parse values from unstructured emails and past these values into an excel table.

Every email that comes in can have the text I am searching for in different locations and the text itself could be stuck to other characters.

For example:

Email 1: "please re-evalue the version you gave us, 20250313142905 again"

I would want to paste 20250313142905 in the excelsheet as a value (column "version")

I have a solution to this (thanks Chriddle) from the following question in the forum:

Solved: Extracting longer unknown string with a fixed length from a known shorter string

HOWEVER. This solution only works in specific cases.

The current expression to parse the string is [please see forum entry above]:

split(
xpath(
xml('<insignificant/>'),
concat(
'translate("',
replace(
replace(
outputs('Compose'),
'"', ' '
),
decodeUriComponent('%0A'), ' '
),
'", ".,;:!,-", " ")'
)
),
' '
)

@and(
startswith(item(), '202'),
equals(length(item()), 14),
isInt(item())
)

I tested the following cases where the value is touching/stuck to anything else

Example 1: 20250313142905+00

Example 2: [space bar][space bar][space bar][space bar]20250313142905

Example 3: 20250313142905Ver

Etc

These don't work and my excel sheet does not get populated.

Can someone please assist?

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (0)

David_MA 14,078 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

I have not used the action, but I do recall other posts where people have similar issues. One question, is the number always 14-digits long? If so, check out this connector: RegexFlow Regular Expression - Connectors | Microsoft Learn. With it, I believe you could first convert the e-mail to plain text and then use a regular expression in this action to find the value using one of these expressions: \b\d{14}\b or \b\d{14}(?=\D|$)

You can test this expression at: regex101: build, test, and debug regex, which will also explain the expressions above.

Was this reply helpful? Yes No
Chriddle 8,672 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Example 1: 20250313142905+00

Example 2: [space bar][space bar][space bar][space bar]20250313142905

Example 3: 20250313142905Ver

Example 1 does not work because “+” is not included in the list of characters to be replaced.

Just add it:

split( xpath( xml('<insignificant/>'), concat( 'translate("', replace( replace( outputs('Compose'), '"', ' ' ), decodeUriComponent('%0A'), ' ' ), '", "+.,;:!,-", " ")' ) ), ' ' )

I don't know what the point is in example 2, just the square bracket? Same as the plus. Add it to the characters to replace.

In Example 3, is it always "Ver" behind the number? If so, use an additional replace function to replace it with space (or add V to the characters to replace).

Was this reply helpful? Yes No
AY-13021440-0 29 on at

Like (0)

Report
Copy link

Link copied!

Hi Chriddle

The point I tried to make is: a positive result depends exclusively on the characters before and after the values I am looking for. This is a problem as the text is so unstructured, there will always be failures.

It might be better to create an expression searches for "202". If that is found to count from the first "2" 14 digits long and split that as the value "Version"

there are so many exceptions, we can't catch them all

One of the emails I just received is:

"please send the upated version (2026139200456) back to us ASAP"

I can't add ) or ( to the exceptions as they look like they prematurely opening and closing something.

Would parsing the email text in JSON help and then getting the values I need?

Was this reply helpful? Yes No
David_MA 14,078 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Based on the discussion so far, I think the regex action is your best bet. You can test out various scenarios on the site I posted in my original post with one of the two expressions. I tested the various samples you provided, and the second expression finds the 14-digit number in all of your samples.

Was this reply helpful? Yes No
Chriddle 8,672 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
One of the emails I just received is:
"please send the upated version (2026139200456) back to us ASAP"
I can't add ) or ( to the exceptions as they look like they prematurely opening and closing something.

I don't get it ;)

Why can't you just add brackets to the xpath's translate?

Just replace this

"+.,;:!,-"

with

"V[]()+.,;:!,-"

(and add other chars that might be next to your numbers).

That's the basic idea of this approach: replacing such characters with a space and then splitting the text at the space to get the words.
Then filter these words for the wanted number.

Was this reply helpful? Yes No
AY-13021440-0 29 on at

Like (0)

Report
Copy link

Link copied!

Hi guys

First of all, thank you David for contributing as well. I am unsure on how to use regex exactly for free within the flow I am trying to automate. It seems I would need to purchase a connector and I can't do that.

Second, back to Chriddle :-)

So I tried a flow with the following text:

"

version:           20250213065925+00

ZP:       DE0007449909611XGSE0H000000PA6SLS

Dude Company AG"

Ang got the following errors:

Problem 1: As you can see, the + was removed from the original email and it was transformed to 2025021306592500, which is now no longer 14 digits

Problem 2: the "ZP" Value I am also trying to extract is in it's own value now as "BilanzkreissummenZP       DE0007449909611XGSE"

That dosen't help either

Hence, my Compose 1 is empty:

My split looks like this now for both Version and ZP:

split(

xpath(

xml('<insignificant/>'),

concat(

'translate("',

replace(

replace(

outputs('Compose'),

'"', ' '

),

decodeUriComponent('%0A'), ' '

),

'", "V[]()+.,;:!,-", " ")'

)

),

' '

)

Best

Amr

Was this reply helpful? Yes No
Chriddle 8,672 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

You are trying to find different values, each of which requires its own search pattern.
This also applies to a regex search.

First, you should create an overview of which types of values you are looking for and how to identify them.

For example, it may be obvious to you why "DE0007449909611XGSE0H000000PA6SLS" should result "DE0007449909611XGSE".

For me, it is not. Is it connected to an ISIN?

Was this reply helpful? Yes No
AY-13021440-0 29 on at

Like (0)

Report
Copy link

Link copied!

Hi Chriddle,

I don't know what "ISIN" is.

Regarding this : "
For example, it may be obvious to you why "DE0007449909611XGSE0H000000PA6SLS" should result "DE0007449909611XGSE".

For me, it is not. Is it connected to an ISIN? "

It is clearly obvious, that the value I am looking for: (DE0007449909611XGSE0H000000PA6SLS) was parsed incorrectly (see screenshot).

Hence my questions below: can you assist on question 1 and 2? For question 1 there is a + that I thought we captured in your expression. Here the plus disappears and adds 2 extra 0s.

Best

Was this reply helpful? Yes No