formula to remove random text from a specific character to the next specific character

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

Im looking for some help regarding formulas to remove unwanted part of an array.

In PowerApps i recieve an array like this:

{
"A/": null,
"B/": "Teltonika.jpg",
"C/": "Salmon|8a785cf0-eda2-45d3-8fb5-e8fda2a9cae9;Crabstick|6ac61548-b810-4edc-924b-10acea2a794d",
"D/": "Fishing from boat|868f04fb-be45-44cf-8299-3ea9319d1378;Egne|4811f655-f68e-4227-960e-ee0b51a575b2",
"E/": "Line|eebf6781-2aa1-4334-866c-85e065522eb1;Not|7d59fc8d-062d-4fac-98eb-417dc5bff1fc",
"F/": "Norway|2349f5bf-0102-4be4-b7c6-fe825a9b90ec;Sweden|00e1e618-fff8-4ecf-890b-d934b8d13ed8",
"G/": "Sun|9da97257-73be-478e-8035-32c6020d66f5;Snow|077639fa-dc96-4340-991a-c05acc3435f5",
"H/": "John Doe",
"I/": "Fishing rod|83ed3581-b448-436a-9186-3831e845e3f4;Net|749f935e-9b3a-4c83-8eb1-322a4cd357ec",
"J/": "Beach|3ef93644-85ba-4637-98c7-2cc4021eddbd;Coast|10a88b39-9557-4a01-852a-c7cd2e4567bc",
"K/": 423,
"L/": "People|9bc0e261-d07d-4de8-9ed3-90aea45c7330;Animal|a4139bbf-9909-4387-a90c-bbd949b1182c"
}

The above array is retrieved from a SharePoint list (no i cant connect PowerApps to SharePoint, and i have to do it like this 🙂 )

What i would like to achieve is to search for some characters, and remove everything in between them.

For an example on the part of the array starting with "J/".

I need to somehow find the character | and the ; and then remove everything in between.

So the goal is to have this text: "J/": "Beach|3ef93644-85ba-4637-98c7-2cc4021eddbd;Coast|10a88b39-9557-4a01-852a-c7cd2e4567bc",

come out like this: "J/": "Beach, Coast,

Sometimes the array will contain only one word. But other times it could contain 10 words. All of which i would like to "extract" withouth the unwated text.

The ultimate goal is to trim away the unwanted text. Then write the values into an collection. And this collection is to be used in a gallery

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (2)

Sort by

eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

You have to use the split function. Mr Dang has a video which will help.
https://youtu.be/L4pPCDXoL7c

Please Mark as a solution for other people's benefit if it resolved your request. Give Thumps up if it helped.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for reply. But i have checked his videos.

But this should be possible to solve with some text formulas.
I have this string in a label:
Salmon|8a785cf0-eda2-45d3-8fb5-e8fda2a9cae9;Crabstick|6ac61548-b810-4edc-924b-10acea2a794d

I would like to remove everything from | until the ; and then from | until the end

The only thing i have come up with so far is. In a labels Text property:
Concat(FirstN(Split(ThisItem.Arter, "|").Result,
CountRows(Split(ThisItem.Arter, "|").Result)-2),Result & ", ")

But the output from this is: Salmon,

Was this reply helpful? Yes No
eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

From this,
Salmon|8a785cf0-eda2-45d3-8fb5-e8fda2a9cae9;Crabstick|6ac61548-b810-4edc-924b-10acea2a794d

What is the desired output you expect

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

The desired output i expect is one of this

Salmon Crabstick
Salmon; Crabstick

Additional info. Semicolon is always infront of the next value. And the pipeline is always at the end of all the exepected values

Was this reply helpful? Yes No
Verified answer

v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Do you want to get "Salmon Crabstick" from "Salmon|8a785cf0-eda2-45d3-8fb5-e8fda2a9cae9;Crabstick|6ac61548-b810-4edc-924b-10acea2a794d"?
I've made a similar test for your reference:

1)set label1's Text:

"Salmon|8a785cf0-eda2-45d3-8fb5-e8fda2a9cae9;Crabstick|6ac61548-b810-4edc-924b-10acea2a794d"

2)set label2's Text:

Concatenate(First(Split(Label4.Text,"|")).Result," ",Last(Split(Last(FirstN(Split(Label4.Text,"|"),2)).Result,";")).Result)

Best regards,

Was this reply helpful? Yes No
Verified answer

eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

Concatenate(Left(TextInput1.Text,Find("|",TextInput1.Text)-1), Left(Right(Substitute(TextInput1.Text,";",Char(9),1),Len(TextInput1.Text)-Find(Char(9),
Substitute(TextInput1.Text,";",Char(9),1),1)+1),Find("|",TextInput1.Text)+3))

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Perfect, thank you 🙂

I added & "," to separate the two values by comma

Concatenate(First(Split(Label4.Text,"|")).Result & "|"," ",Last(Split(Last(FirstN(Split(Label4.Text,"|"),2)).Result,";")).Result)

Was this reply helpful? Yes No
Voltron 67 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-yutliu-msft if my data multivalues? over two
textA|8a785cf0-eda2-45d3-8fb5-e8fda2a9cae9;textB|8a785cf0-eda2-45d3-8fb5-e8fda2a9cae9;textC|...;textZ|...
please help

Was this reply helpful? Yes No