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

(2) Share

Report

Posted on by AY-13021440-0

Hi community,

I require help with the following challenge for work:

I receive unstructured emails from partners and the content structure is always different. I need to parse specific values from these emails and paste them in an excel sheet.

What is common in every email:

There is a value, let's call it order, that always starts with 202... but is 14 digits in total. The is another value called "technical sum" which always starts with 434 and is always 32 digits in length in total.

Emails do NOT always have the following form:

order: 202xxxxxxxxxxxx

technical sum: 434xxxxxxxxxxxxxxxxxxx

The info is just there somewhere.

I need a flow that will extract (trigger: email arrives) the information (and if there are are multiple orders and multiple technical sums per email, i need them as well) and extract it

Nothing is working in power automate. I don't have access to AI prompts (premium product)

I can't build any level of expressions that are able to search for these values and extract them.

ChatGPT and Copilot have been unfortunately not in any way helpful.

I can create the first step (trigger)

and the second step (html to text)

This is urgent, if possible.

If anyone needs me to elaborate on any detail please let me know. I really need the help and will be active in this chat until the issue is resolved.

Everything in youtube, etc works great with structured data. I didn't think that searching through unstrcutured data would be this tough.

Best

Amr

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Chriddle 8,672 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!
Spit your text into words and filter for words that meet the condition:

From:

This replaces letters that could be directly next to the numbers you are looking for (e.g. comma, double quote, line break etc) with a space.

(The XPath transformation function is simply a way to perform multiple replacements without nesting all of the otherwise required calls to the PA's replace function.)

Then split at space

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

Filter:

Filter for words that start with "202", are 14 in length and consist only of numbers.

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

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

Like (0)

Report
Copy link

Link copied!

To AlexEncodian,

I can't reply to your post directly for some reason (I'm new here), but it seems your method is a premium method that needs me to connect to a company called Encodian (?). That won't work for me. But thanks anyways.

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

Like (0)

Report
Copy link

Link copied!

Thanks so much Chriddle!!!

Finally, the answer I am looking for! This actually worked.

2 quick extra questions if I may:

How do I get this output, 2123435543, and have it called "Version" as a value that will later be added automatically into an excel sheet in the column "Version"?

How do I run another Step to the Version one in parallel, as I need to search the text for another value as well (called "technical sum")? It seems power automate lets you run one step at a time(?)

Best

Amr

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

Like (1)

Report
Copy link

Link copied!
You can just "Add a parallel branch" for each Filter action and combine their outputs with a Compose:

Filter array 2

From: Same as Filter array

Filter:

@and( startswith(item(), '434'), equals(length(item()), 32), isInt(item()) )

Compose

{ "order": @{first(body('Filter_array'))}, "technical sum": @{first(body('Filter_array_2'))} }

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

Like (0)

Report
Copy link

Link copied!

Hi Chriddle,

Unfortunately I have 2 problems now:

First of all, lets forget the term "technical sum". I want to populate an excel sheet with the column known as "ZP". This ZP value keeps coming back as null:

Characteristics of ZP are: 33 characters long and starts with "403"

This is what I'm using in the second parallel array:

Here is the text that needs to be parsed:

"

Please check the following issues:

Month: 10.2024 - MaBiS-ZP: 4033872000058BKA00000001923160ELG -
Version: 20241231045512

Month: 07.2024 - MaBiS-ZP: 4033872000058BKA00000001923160ELG -
Version: 20241231085555

Month: 09.2024 - MaBiS-ZP: 4033872000058BKA00000001923160ELG -
Version: 20241231084212 "

Second issue is: if there are multiple "versions", how do i get, in the example above all 3?

How would I get back all 3 ZPs?

Best

Amr

P.S: Thanks a million

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

Like (1)

Report
Copy link

Link copied!

Obviously this is not an Interger: 4033872000058BKA00000001923160ELG

So remove function isInt() from the filter (or check only the first 30 characters, see substring())

You could also check with endsWith(item(), 'ELG')

To get all version numbers, just remove the function first() from the expressions that get the values from the filter.

Was this reply helpful? Yes No