cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sassyx1965
Frequent Visitor

Help needed splitting text with same delimiter from an email

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
eric-cheng
Solution Sage
Solution Sage

Hi @sassyx1965 ,

 

ericcheng_2-1634190779413.png

 

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]

ericcheng_3-1634190886672.png

 

--------------------------------------------------------------------------
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

 

 

 

 

View solution in original post

eric-cheng
Solution Sage
Solution Sage

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
 

View solution in original post

9 REPLIES 9
eric-cheng
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

Hi @sassyx1965 ,

 

ericcheng_2-1634190779413.png

 

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]

ericcheng_3-1634190886672.png

 

--------------------------------------------------------------------------
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

 

 

 

 

View solution in original post

sassyx1965
Frequent Visitor

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
Frequent Visitor

@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
Solution Sage
Solution Sage

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

Hi @eric-cheng ,

 

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

eric-cheng
Solution Sage
Solution Sage

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

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
Solution Sage
Solution Sage

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
 

View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (3,582)