Answered

Simple question about using expressions

(0) Share

Report

Posted on by chawk57

I'm completely new and have searched for answers but I feel like I'm a dog groomer trying to get admitted to medical school.

I'm simply trying to parse out the body of a new email (new leads coming in) and populate it into an Excel sheet.

The email body looks like:

Name: Gomer Pyle

Email: xyz dot com

Phone: 999-123-4567

Comments: Can I get an appointment to hear more about your services!

The spreadsheet I've created have those same 4 columns. I just want to add a row to the spreadsheet. I'm lost

Categories:

General topics

All responses (4)

Answers (1)

Sort by

v-liwei-msft Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @chawk57

I have made a test for your reference:

1.

2.

Name:

trim(replace(first(split(replace(outputs('Html_to_text')?['body'],decodeUriComponent('%0A'),''),'Email')),'Name:',''))

Email:

trim(first(split(last(split(replace(outputs('Html_to_text')?['body'],decodeUriComponent('%0A'),''),'Email:')),'Phone:')))

Phone:

trim(first(split(last(split(replace(outputs('Html_to_text')?['body'],decodeUriComponent('%0A'),''),'Phone:')),'Comments:')))

Comments:

trim(last(split(replace(outputs('Html_to_text')?['body'],decodeUriComponent('%0A'),''),'Comments:')))

3.

Best Regards,

Levi
chawk57 5 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks you so much. That got me much further. However, I failed to tell you that the beginning of the email has an intro section in the body. It looks like this:

Below is the result of your feedback form located at:
{it lists my website URL}
Sent from: 70.233.162.79
======================================================================

Name: Gomer Pyle

Email: xyz dot com

Phone: 9991234567

Comments: Test #7

The email, phone and comments populated my spreadsheet perfectly. But the Name was populated with all of that introductory information. So, I believe the Name field is the only one that I need additional help to get that formula correct.

I suppose it would be good if I also capture the IP Address from the intro section + I would love to format the phone number as (999) 123-4567 but both of those items are low priority. Thanks again!
Verified answer

v-liwei-msft Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @chawk57

For Name, please try:

trim(first(split(last(split(replace(outputs('Html_to_text')?['body'],decodeUriComponent('%0A'),''),'Name:')),'Email')))

Best Regards,

Levi
TryingToPwrAuto 13 on at

Like
a
(0)

Report
Copy link

Link copied!

This also (partially) worked for my project, thank you!

This pulls the first result/row perfectly. If my email has sometimes 1 to 100 results on the same email, all ended/seperated by "\n" at the end of the line - how do I "repeat" this function to grab/parse all strings?