Extract specific part of text

(0) Share

Report

Posted on by ADP5

Hello,

Suppose I have the following string output: abcdefgdd.mm.yyyyhijklmn

Let's say it's part of a longer text where "abcdefg" repeats.

I'd like to extract "dd.mm.yyyy", which always has the same number of characters even though the characters will differ, using "hijklmn" as a delimiter (("hijklmn") does not repeat in the text).

I need an expression that counts and extracts text from right to left. I tried using substring but negative index is not allowed. I somehow managed to do it applying slice to a shorter part of the entire string, but when I incorporated it from the test flow into the main flow with the whole text, it didn't give any result.

Any help would be much appreciated.

Categories:

Building flows

I have the same question (0)

All responses (13)

Answers (1)

Sort by

Verified answer

eliotcole 4,390 Moderator on at

Like (0)

Report
Copy link

Link copied!
If 'abcdefg' is identical in each repeat, then you can split() on that.

Use a split() expression with the source of all the text as the first part, and 'abcdefg' is the splitter ... then skip the first pointless value ... something like this:

skip( split( REFERENCE_TO_YOUR_SOURCE_TEXT, 'abcdefg' ), 1 )

Replace 'REFERENCE_TO_YOUR_SOURCE_TEXT' with a reference to whatever holds the text.

That will create a simple array of strings where the first 10 characters will be what you want. So you could use a Select action with the above in the 'From' field, and (tap the little pen on the right) a 'Map' value of:

slice( item(), 0, 10 )

If you want to make that a usable date value, then you could also do this:

concat( slice( item(), 6, 10 ), '-', slice( item(), 3, 5 ), '-', slice( item(), 0, 2 ) )

Here that is in action:

With the result:

I used this as my Select from' value:

skip( split( outputs('SourceText'), 'abcdefg' ), 1 )

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report
Copy link

Link copied!
If the text "abcdefg" is always the same length, then you can use slice.

slice({textvalue},7,17)

Was this reply helpful? Yes No
eliotcole 4,390 Moderator on at

Like (0)

Report
Copy link

Link copied!

If 'abcdef' is not the same everytime then you are going to have a harder time, and might want to look into a way to use a regular expression somehow, @ADP5 ...

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (1)

Report
Copy link

Link copied!
ah @eliotcole I just thought about using the periods as a reference 💡

@ADP5 here is an expression that doesn't matter the length of the text. It functions by referencing where the periods '.' are located. So long as the format dd.mm.yyyy stays the same with the '.' periods then this will work.

slice({textvalue},sub(int(indexOf({textvalue},'.')),int(2)),add(int(nthIndexOf({textvalue},'.',2)),5))

https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#nthIndexOf

Was this reply helpful? Yes No
eliotcole 4,390 Moderator on at

Like (0)

Report
Copy link

Link copied!

Nice one, @wskinnermctc ... 🏆

Although ... 🤔 ... if I'm reading @ADP5's request rightly ( and I always get things wrong 😅 ) he's asking for each instance of the date in the text. Does yours account for that?

Take a look at the text that I fed in to my example ... that's potentially what I believe they are looking at ... and other more randomised versions, of course.

I can't think of a way to do this *easily* if the 'abcdefg' is not identical before each date.

However I could've sworn I saw something relating to regex recently ... maybe there is a regex hack out there?

Was this reply helpful? Yes No
eliotcole 4,390 Moderator on at

Like (0)

Report
Copy link

Link copied!

Perhaps this needs some office scripts work like what @Paulie78 did here:

https://www.tachytelic.net/2021/04/power-automate-regex/

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (1)

Report
Copy link

Link copied!

I don't know. I was just thinking simple terms like single text strings. I guess we'll see when OP replies.

(This is speculation and I'm not going to do it; more like entertainment purposes. But if it is like each instance in your example. I wonder if you could get away with counting the number of '.' periods and referencing them like that. *It must only contain periods at the date values* I bet it could be done in some wizard method, since if you split it, there would always be 3 sections for every date.

Anyways, making my head hurt think about it.)

Was this reply helpful? Yes No
eskay 43 on at

Like (0)

Report
Copy link

Link copied!

@eliotcole

I think what you saw were Regex lookaheads.They would work like this:

Input: abcdefgdd.mm.yyyyhijklmn
Regex: .{10}(?=hijklmn)
Output: dd.mm.yyyy

.{10} = Take ten elements of whatever kind
(?=) = which are before the following
hijklmn = String

Was this reply helpful? Yes No
eliotcole 4,390 Moderator on at

Like (0)

Report
Copy link

Link copied!

Aye ... that would be nice.

But I really do remember seeing them utilised in Power Automate / Logic Apps somewhere, @eskay, that was more what I was chatting about.

On that note, though, if @ADP5 can use Logic Apps, they might be able to leverage more, there. Either via a code block, or a Function App integration. If this is not a commonly run thing, it would only ever end up costing .0000000000000000000003 pence per run or whatever it is for the few actions it would cost. 😉

((( @wskinnermctc ☝️)))

Was this reply helpful? Yes No
ADP5 45 on at

Like (0)

Report
Copy link

Link copied!

@eliotcole @wskinnermctc @eskay @AlexEncodian Thank you very much for your replies. I'm giving you the text below, to be more specific. The part I want to extract is in bold and it appears two times within the text. I only need one of the instances, it doesn't matter which one.

{"@odata.type":"#Microsoft.Dynamics.CRM.expando","displayName":"CityDetails","entries@odata.type":"#Collection(Microsoft.Dynamics.CRM.crmbaseentity)","entries":[{"@odata.type":"#Microsoft.Dynamics.CRM.expando","Details":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","value":"Utility - (2.245.985,48-7.546.617,04)-(2.059.932,98-","displayName":"Details","fieldType":"string","text":"Utility - (2.245.985,48-7.546.617,04)-(2.059.932,98-","location":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","pageNumber":1,"boundingBox":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","left":0.0417478438782518,"top":0.4707381475522014,"width":0.3757306021169195,"height":0.013254184496480192,"polygon":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","coordinates@odata.type":"#Collection(Microsoft.Dynamics.CRM.crmbaseentity)","coordinates":[{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.0417478438782518,"y":0.4707381475522014},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.4174784459951713,"y":0.4707381475522014},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.4174784459951713,"y":0.4839923320486816},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.0417478438782518,"y":0.4839923320486816}]}}}}},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","Details":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","value":"7.458.419,03) = 97.854,49 kWh (Company Country SRL:","displayName":"Details","fieldType":"string","text":"7.458.419,03) = 97.854,49 kWh (Company Country SRL:","location":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","pageNumber":1,"boundingBox":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","left":0.04235288451895699,"top":0.4835648079623782,"width":0.3811759678832663,"height":0.012826619635347425,"polygon":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","coordinates@odata.type":"#Collection(Microsoft.Dynamics.CRM.crmbaseentity)","coordinates":[{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.04235288451895699,"y":0.4835648079623782},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.42352885240222327,"y":0.4835648079623782},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.42352885240222327,"y":0.49639142759772564},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.04235288451895699,"y":0.49639142759772564}]}}}}},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","Details":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","value":"30.11.2023)","displayName":"Details","fieldType":"string","text":"30.11.2023)","location":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","pageNumber":1,"boundingBox":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","left":0.04235288451895699,"top":0.5070803044770681,"width":0.0828906435094717,"height":0.014964443941011263,"polygon":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","coordinates@odata.type":"#Collection(Microsoft.Dynamics.CRM.crmbaseentity)","coordinates":[{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.04235288451895699,"y":0.5070803044770681},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.1252435280284287,"y":0.5070803044770681},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.1252435280284287,"y":0.5220447484180794},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.04235288451895699,"y":0.5220447484180794}]}}}}},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","Details":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","value":"97.854,49 kWh) x 0,69351CUR [#HBC3-electric] (01.11.2023 -","displayName":"Details","fieldType":"string","text":"97.854,49 kWh) x 0,69351CUR [#HBC3-electric] (01.11.2023 -","location":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","pageNumber":1,"boundingBox":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","left":0.04235288451895699,"top":0.49553633865028957,"width":0.4235288415832432,"height":0.014109314218745728,"polygon":{"@odata.type":"#Microsoft.Dynamics.CRM.expando","coordinates@odata.type":"#Collection(Microsoft.Dynamics.CRM.crmbaseentity)","coordinates":[{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.04235288451895699,"y":0.49553633865028957},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.4658817261022002,"y":0.49553633865028957},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.4658817261022002,"y":0.5096456528690353},{"@odata.type":"#Microsoft.Dynamics.CRM.expando","x":0.04235288451895699,"y":0.5096456528690353}]}}}}}],"columns@odata.type":"#Collection(Microsoft.Dynamics.CRM.crmbaseentity)","columns":[{"@odata.type":"#Microsoft.Dynamics.CRM.expando","name":"Details"}]}

The date (which I want to extract) varies, so it's not the same in all texts. The reason I tried to extract it using a delimiter/index that is after it (on the right side of the date) and not before it (on the left side of the date) is because the delimiters/indices after it repeat multiple times in the text.

Was this reply helpful? Yes No