cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Betsie
Regular Visitor

split string

I receive emails with in the body the next informatie

 

Organisation Microsoft Function PR director firstname Jan - Peter lastname van den Hooghe

 

And in an other body

Organisation Google Analytics Function Dishwasher firstname Michael lastname Gordon

 

I want to spilt the body, but how can I do this, I can't split on the ' '. Because the Function is PR director in example 1 belongs together.  In example 2 belongs Google Analytics together.

Is there a way I can spilt it en send it separate to an new row in Excel?

 

Thank you for thinking along

Betsie

8 REPLIES 8
Expiscornovus
Super User
Super User

Hi @Betsie,

 

You could use a couple of splits on the labels instead of the space characters.

 

Below is an example

I used a variable, replace that by your body field.

 

Organisation

split(split(variables('Body'), 'Organisation ')[1], 'Function ')[0]

 

Function

split(split(variables('Body'), 'Function ')[1], 'firstname ')[0]

 

firstname

split(split(variables('Body'), 'firstname ')[1], 'lastname ')[0]

 

lastname

split(variables('Body'), 'lastname ')[1]

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


grantjenkins
Community Champion
Community Champion

See example below that should get what you want.

 

grantjenkins_0-1669811912102.png

 

Compose contains your data you're trying to parse.

grantjenkins_1-1669811941001.png

 

Below are the expressions used for the Compose actions (Organisation, Function, First Name, Last Name):

//Organisation
trim(replace(slice(outputs('Compose'), indexOf(outputs('Compose'), ' '), indexOf(outputs('Compose'), 'Function')), 'Organisation', ''))

//Function
trim(replace(slice(outputs('Compose'), indexOf(outputs('Compose'), 'Function'), indexOf(outputs('Compose'), 'firstname')), 'Function', ''))

//First Name
trim(replace(slice(outputs('Compose'), indexOf(outputs('Compose'), 'firstname'), indexOf(outputs('Compose'), 'lastname')), 'firstname', ''))

//Last Name
trim(replace(slice(outputs('Compose'), indexOf(outputs('Compose'), 'lastname')), 'lastname', ''))

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

@Expiscornovus I should have refreshed my page before posting. Your solution is a bit more elegant than mine 🙂



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
Expiscornovus
Super User
Super User

Hi @grantjenkins,

 

No worries. It is better to have two solutions than no solutions at all 😁



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Thats right the soliution from @Expiscornovus is elegant, but I get a error message.

Flow save failed with code 'InvalidVariableOperation' and message 'The inputs of workflow run action 'Compose_2' of type 'Compose' are not valid. The variable 'Body' must be initialized before it can be used inside action 'Compose_2'.'

What can I do to fix this error.

 

The solution of @grantjenkins is working. 

 

Thanks both of you to help me.

Expiscornovus
Super User
Super User

Hi @Betsie,

 

Like I mentioned in my previous reply.

 

The example I shared uses a Initiale variable action with the name Body which you need to replace by your dynamic content field to make my example work in your own setup.

 

Did you add that action to your flow or did you change the expressions for your setup?

 

Edit: added screenshot for clarification

 

example_splitstring.png

 

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Yes, it works for 99%. After lastname there is more in the body. Sometimes it starts with utm_source but it can also be utm_handle. 

If I type "split(variables('Compose'), 'Lastname ')[1]" at the end then I get the rest of the text as well, but I don't want that. How can I change this?

And in my excel column I want de date that the mail has arrived. I use received time now, but then I also get the time. Is there an other solution?

Expiscornovus
Super User
Super User

Hi @Betsie1,

 

What is normally directly after the lastname field? Is it separated by a carriage return or newline?

 

The key is to find a separator which you can use to split it again 🙂

 

If there is a newline character, you could use the below:

split(split(variables('Body'), 'lastname ')[1], decodeUriComponent('%0A'))[0]

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,366)