split string

[Betsie]

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

[Expiscornovus]

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]

[grantjenkins]

See example below that should get what you want.

Compose contains your data you're trying to parse.

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', ''))

[grantjenkins]

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

[Expiscornovus]

Hi @grantjenkins,

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

[Betsie]

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]

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

[Betsie1]

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]

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]