Need to search a string starting with "CRQ" and print it to my Excel table.

[RameshKumarE]

Need Flow expression to search for a string starting with "CRQ" and print it to my Excel table.

For Ex, from the below-mentioned Email Subject, need to copy the string that Starts with 'CRQ' and update it to my excel table.

FW: RING HEAL CRQ000001144185 - C10/287014 - TDD27564 - Site closure Project.

Thanks in advance.

[RameshKumarE]

@eliotcole can you able to help me please

[RameshKumarE]

@Pstork1 Can you able to help me please

[eliotcole · Accepted solution]

Hi, @RameshKumarE, this kind of thing is something that you'll quickly learn if you just start messing around with Power Automate. So my primary advice is to ... have a go! 😉 👍

Usually it's good for you to actually have a go, first, and then upload some screenshots of what you want to do here to provide folks that can help with good context, and show at what level you're at with your flow making.

(plus it shows that you're also not just farming out the work 😜)

Here I'll give you some simple advice that will help you build that first, then you can maybe update the question to be a bit more specific.

Basic Flow

This will be roughly (not exactly, I don't know what you have or what you really want to do) what this will look like.

How To Make That

These basic ... general ... steps outline what is done there:

1. Subject Filter - Make sure that your When a new email arrives trigger has "CRQ" (no quotes) in this field
2. crqVAR - Make a Initialize variable action, and choose the type String, and name it crqVAR
   • Place the following expression in the expression builder or see if the copy/paste works straight into the Value field.

     expression

     concat( 'CRQ', split( triggerOutputs()?['body/subject'], 'CRQ' )[1] )

     copy/paste

     @{concat( 'CRQ', split( triggerOutputs()?['body/subject'], 'CRQ' )[1] )}

3. Add a row into a table - Use the Excel action Add a row into a table to add this into your Excel sheet's table

I obviously can't guide you much more than that because I don't know what your specifics are, but equally, it helps to never apply too much specificity to these things. This should suffice to get your first flow running ... just run some test runs, perhaps on a test excel sheet. 👍

If the expression looks complicated, don't sweat on it too much. Break it down in your head logically, the below spoiler can maybe help with that working from the inside out.

Spoiler

1. split()[1] - The split() function literally splits a string of text defined before the first comma into an array of items/entries based upon the value after the comma.
   • Here that is the trigger's Subject field, split on 'CRQ', which will produce at least 2 entries in the array.
     The [1] after the split is selecting the second of those entries.
   • So in your example data above that would produce:
     000001144185 - C10/287014 - TDD27564 - Site closure Project.
     
     
2. concat() - This is just short for concatenate, and it means to join strings of text together.
   
   Here you're joining the text "CRQ" to the text produced by the split() function. Which finally provides the string:
   CRQ000001144185 - C10/287014 - TDD27564 - Site closure Project.

That's all I got!

[RameshKumarE]

Thank you so much for your Valuable Solution @eliotcole.

I'm Converting my Question more specific now,

For the below-mentioned received Mail subjects,

FW: RING HEAL CRQ000001144185 - C10/287014 - TDD27564 - 15/07/22 19:00GMT - 16/07/22 05:00 GMT for the DCS Site closure Project.
RE: CRQ000001144186, TDD 27564, TDD 27565, TDD 27566, C10-287015, C10-287016 - 21/7/22 20:00-22:59 GMT CDP Site closure Project.

Expecting an Excel Autoupdate Output like the Below mentioned Sample,

Supporting Notes:-
CRQ Ref no always will be in 15 Length characters,
TDD Ref No -  5 Length No's,

C10 Ref - 6 Lenght No's
Outage week No - Start date's Week of the Month
Project manager name - Mail Sender Name,
Project name - Site Closure Project (along with Site Names like CDP, and DCS)

Thought to get flow Expression to get CRQ REF alone, then to follow the same to columns, if there any other simple way to write the expression also please share.

Thanks a lot again.

[RameshKumarE]

Yes, I have tried your solution by pasting the provided expression in the CRQ cell as per below,
and received the same output as you have shown.

[eliotcole]

I think raise this as a separate question, @RameshKumarE, as this one has been resolved, now, and you'll get more responses to it in a new question, mate.

But why don't you keep playing with test runs, and try to see if you can figure out whether or not to split the string by ' - ' or ', ' ... 😉 👍

[RameshKumarE]

yes, trying now with other string expressions to see how the output varies, and sure will raise it as a new question to get other ways of cracking the expressions to get solutions.

Thanks Mate 🙂 

[RameshKumarE]

@eliotcole, Finally got what I need. Thanks for your encouragement.

concat('CRQ',trim(split(split(triggerBody()?['subject'],'CRQ')[1],' ')[0]))