Need help with split/first/last

[Time1]

Hi all,

I'm trying to build an automated flow to populate a excel spreadsheet.

The only part I need help with is the formula (the exact thing to copy and paste would be a huge huge help) to split the "12345" out of this text.

20230515_A referral for patient 12345 has been raised and awaits your attention. This referral is for ....

It will always be:

YYYYMMDD_A referral for patient "12345" has been raised and awaits your attention...

The "12345" does vary in length (always between 4 and 6 digits).

If anyone has a good guide on how to use the split/first/last function that would be a huge help too! I find it so confusing.

Thanks in advance, this will be a huge help!

[ekarim2020 · Accepted solution]

Hi @Time1 ,

One way to do this is to split the string at the space character. The result will be an array of strings. You just need to extract the fourth element of the array to get the digits:

 

 

The expression in the compose action is:

split(variables('varText'),' ')?[4]

 

Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site

[AlexEncodian]

@Time1 

If you want a quick one-step solution, you can use Encodian's Utility - Extract Text between Values action. Super easy and will work with different lengths of characters you want to extract:

In start value: referral for patient 
In end value: has been raised

Trim Result: whether to trim white space at the beginning and end of extract string.

FYI – Encodian actions with the “Utility“ prefix only consume 1/20th of Encodian action count (apart from on free plan). For example, a Standard plan has 500 actions (so utility actions can be used 500x20 = 10,000 times).

[Time1]

That's brill, thanks! I'll use the split variables thing in future as well

[Time1]

What would the split formula be for 'everything including + after word number 6'?

[ekarim2020]

>>What would the split formula be for 'everything including + after word number 6'?

Sorry, I didnt understand.

Could you please give me an example of the text?

Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site

[Time1]

Thanks for replying, sorry for not being specific.

For example, the text could be:

20230515_A referral for patient 12345 has been raised and awaits your attention. This referral is for CT Scan

20230515_A referral for patient 12345 has been raised and awaits your attention. This referral is for Assessment + treatment

20230515_A referral for patient 12345 has been raised and awaits your attention. This referral is for A long bit of text that I can't think of an example for that includes lots of words

My objective is to get everything after the word 'for'/after word 16, sorry I didn't count and just used 6 as an arbitrary number)

[ekarim2020]

You could use the following:

Last(split(variables('varText'),'This referral is for '))

For example:  "20230515_A referral for patient 12345 has been raised and awaits your attention. This referral is for CT Scan" should return "CT Scan"

Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site

[Time1]

Amazing, I think I'm starting to get the Split + Last function. Thanks for your help!