Help needed splitting text with same delimiter from an email

[sassyx1965]

Here is an example of the email text body I really need help with splitting if it is possible. 

Text to split:

The work order for Mary Lee-Michael Terry 4217 Liberty-BR476598 (WO# 1754961)

(The highlighted areas are the data I am trying to capture)

Mary Lee (Customer Name) this I can extract successfully

Michael Terry (Contractor)

BR76598 (Customer ID)

1754961 (Work Order No)

Thank you

[eric-cheng]

Hi @sassyx1965 ,

Customer Name, Customer ID and Work Order No would be fairly easy.  The contractor may be a problem.  What is 4217 Liberty?

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

[eric-cheng · Accepted solution]

Hi @sassyx1965 ,

1. Compose - passed the value of your string

2. Compose 2 - performed a split on hyphen -

@split(outputs('Compose'),'-')

3. Customer Name

@trim(first(outputs('Compose_2')))

4. Customer ID

@substring(trim(last(outputs('Compose_2'))),0,indexOf(trim(last(outputs('Compose_2'))),'(WO#'))

5. Work Order ID

@replace(trim(last(split(trim(last(outputs('Compose_2'))),'(WO#'))),')','')

Contractor Name will be quite hard to distinguish the name from 4217 Liberty but i'll wait for your response to my earlier question.  If it will always have a number after the name, there is a chance.

@outputs('Compose_2')[1]

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

[sassyx1965]

Thank you @Eric for your response. 4217 Liberty is the customers street address. If it is provided it will always start with a number if not provided the next character would be a - and then the Customer ID

[sassyx1965]

@eric-cheng I am accepting this as a solution (worked beautifully), thank you again. 

I am having one issue with 

4. Customer ID

@substring(trim(last(outputs('Compose_2'))),0,indexOf(trim(last(outputs('Compose_2'))),'(WO#'))

Error is: 

The length of substring can't be longer than '27' which is the length of the source string.

[eric-cheng]

Hi @sassyx1965 ,

Can you please take a screenshot of your flow.

Compose 2 should contain the split expression.  Is this for the same piece of text?

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

[sassyx1965]

Hi @eric-cheng ,

The text is not identical but the structure is as it comes from an email. 

[eric-cheng]

Hi @sassyx1965 ,

Thanks.  

The challenge will be a changing structure.  The above assumes that it will always have xxxxxx - xxxxx - (WO#)

You may need to add some conditions and checks and have multiple branches of logic to extract data if required

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

[sassyx1965]

Correct the structure is always xxxxxx - xxxxx - (WO#). Can you explain the expression and maybe I can figure out what is wrong.

@substring(trim(last(outputs('Compose_2'))),0,indexOf(trim(last(outputs('Compose_2'))),'(WO#'))

Thanks

[eric-cheng · Accepted solution]

Hi @sassyx1965 ,

Sure.  See below in red.  Please note that some expressions has a dependency on the previous expression.

1. Compose - passed the value of your string

2. Compose 2 - performed a split on hyphen -

@split(outputs('Compose'),'-')

This expression takes the string from your Compose action.  It will then split the string based on the locations of '-' into an array.  As you string will have 3 '-', it will have 3 items in the array.  

Item 1 = Mary Lee

Item 2 = Michael Terry 4217 Liberty

Item 3 = BR476598 (WO# 1754961)

3. Customer Name

@trim(first(outputs('Compose_2')))

This will take Item 1 from above (first()) and then use trim() to get rid of all the white spaces before and after.

4. Customer ID

@substring(trim(last(outputs('Compose_2'))),0,indexOf(trim(last(outputs('Compose_2'))),'(WO#'))

This will take Item 3 from above (last()) and then use trim() to get rid of all the white spaces before and after. 

It will then use substring() to extract BR476598.  Substring takes in 3 parameters being, the original string, the starting point and how many characters you want to extract.  To get the last parameter, we use indexOf to find the location / location within the string of WO#.

5. Work Order ID

@replace(trim(last(split(trim(last(outputs('Compose_2'))),'(WO#'))),')','')

To get work order ID, we assumed we got the customer ID correctly.  We then take the string from Item 3 and then look for the Customer ID and remove it.  This will leave only the Work Order ID.

Contractor Name will be quite hard to distinguish the name from 4217 Liberty but i'll wait for your response to my earlier question.  If it will always have a number after the name, there is a chance.

@outputs('Compose_2')[1]

To get contractor name, we get Item 2.   To do this we need to reference the index (which starts at 0), so Item 1 = Index 0, Item 2 = Index 1 and Item 3 = Index 3 etc.

Let me know if you need further assistance.

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn