Splitting from Outlook Email Body

[JeffGrant]

Hi All, I have been able to scrape my Outlook emails easy enough to create a file in Excel.
Below is the text that pops up in a cell - No problem there.

Now, trying to manipulate this cell to get ready for an import into our CRM, is giving me some grief.

Doing Find & Replace wont work because VALUES does not appear in the F & R drop down list box.
Doing a Text to Columns does not work, it just something weird.
Using some of the VBA code available does work, because it is looking for Chr(10) or Chr (13) and that just gives a run time error.
I thought I had some success with clearing the format of the cell, because all of the text went to one line but then I couldn't do anything with it.

What I don't understand is what are the hidden characters coming from the Outlook email body.

Ultimately, I am trying to get each line of the below text into its own cell, so I can manipulate it.

Hope someone can help with this one.


<Hospitality and Food Service Equipment - Get Quotes to Compare - HospitalityHub>
A buyer has just requested a quote from Italian Gelato Concepts Pty Limited <Italian Gelato Concepts Pty Limited: Gelato & pastry manufacturing & display equipment - HospitalityHub Australia> on HospitalityHub.

Buyer's Quote Request

* Product Category: Soft Serve Ice Cream Machine <Soft Serve Machine & Frozen Yoghurt Machine in Australia for sale - Get Quotes to Compare Price & Specifications>
* Requirements:
I’m wanting a soft serve machine with a frozen yoghurt combo or something along them lines.
* Where this will be used: Not for profit
* Quantity: 1
* Needed in: Next few weeks

Buyer Details


* Name: Jeff Grant
* Email: JeffGrant@gmail.com
* Phone: 1234567890
* Company: Indigenous community
* Delivery Location: QLD
* Postcode: 4895

How was this enquiry? <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0MzQzJnN0YXRzPTE=> / <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0MzQzJnN0YXRzPTA=>

Manage your content, view leads and track activity Login <- OMA.Web>

Australia's favourite hospitality marketplace Operated by Industracom <Industracom>

To stop getting messages from HospitalityHub login to your Supplier Panel <- OMA.Web> and remove yourself as a contact under settings.

Retrieve your login details <- OMA.Web>


<https://www.hospitalityhub.com.au/Newsservice/EnquiryTrack?enquiryTrackingId=> <https://www.google-analytics.com/co...plier_Email&ea=Open&el=HHQuoterequestsupplier> <http://url6421.industracom.com/wf/o...VZL7DlztsGY9WpEaxxzmlV9TdzSq3KwnzlpSJJCjpo-3D>

 

[Kerryx]

Power query
In the example you provided i pasted to a single cell as below

Book1
	A
1	Column1
2	<Hospitality and Food Service Equipment - Get Quotes to Compare - HospitalityHub> A buyer has just requested a quote from Italian Gelato Concepts Pty Limited <Italian Gelato Concepts Pty Limited: Gelato & pastry manufacturing & display equipment - HospitalityHub Australia> on HospitalityHub. Buyer's Quote Request * Product Category: Soft Serve Ice Cream Machine <Soft Serve Machine & Frozen Yoghurt Machine in Australia for sale - Get Quotes to Compare Price & Specifications> * Requirements: I’m wanting a soft serve machine with a frozen yoghurt combo or something along them lines. * Where this will be used: Not for profit * Quantity: 1 * Needed in: Next few weeks Buyer Details * Name: Jeff Grant * Email: JeffGrant@gmail.com * Phone: 1234567890 * Company: Indigenous community * Delivery Location: QLD * Postcode: 4895 How was this enquiry? <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0MzQzJnN0YXRzPTE=> / <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0MzQzJnN0YXRzPTA=> Manage your content, view leads and track activity Login <- OMA.Web> Australia's favourite hospitality marketplace Operated by Industracom <Industracom> To stop getting messages from HospitalityHub login to your Supplier Panel <- OMA.Web> and remove yourself as a contact under settings. Retrieve your login details <- OMA.Web> <https://www.hospitalityhub.com.au/Newsservice/EnquiryTrack?enquiryTrackingId=> <https://www.google-analytics.com/co...plier_Email&ea=Open&el=HHQuoterequestsupplier> <http://url6421.industracom.com/wf/o...VZL7DlztsGY9WpEaxxzmlV9TdzSq3KwnzlpSJJCjpo-3D>
Sheet1

Then imported to PQ and applied the following

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Split", each Text.Split([Column1],"#(lf)")),
    #"Expanded Split" = Table.ExpandListColumn(#"Added Custom", "Split"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Split",{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Split] <> ""))
in
    #"Filtered Rows"

to get this output

Book1
	A
1	Split
2	<Hospitality and Food Service Equipment - Get Quotes to Compare - HospitalityHub>
3	A buyer has just requested a quote from Italian Gelato Concepts Pty Limited <Italian Gelato Concepts Pty Limited: Gelato & pastry manufacturing & display equipment - HospitalityHub Australia> on HospitalityHub.
4	Buyer's Quote Request
5	* Product Category: Soft Serve Ice Cream Machine <Soft Serve Machine & Frozen Yoghurt Machine in Australia for sale - Get Quotes to Compare Price & Specifications>
6	* Requirements:
7	I’m wanting a soft serve machine with a frozen yoghurt combo or something along them lines.
8	* Where this will be used: Not for profit
9	* Quantity: 1
10	* Needed in: Next few weeks
11	Buyer Details
12	* Name: Jeff Grant
13	* Email: JeffGrant@gmail.com
14	* Phone: 1234567890
15	* Company: Indigenous community
16	* Delivery Location: QLD
17	* Postcode: 4895
18	How was this enquiry? <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0MzQzJnN0YXRzPTE=> / <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0MzQzJnN0YXRzPTA=>
19	Manage your content, view leads and track activity Login <- OMA.Web>
20	Australia's favourite hospitality marketplace Operated by Industracom <Industracom>
21	To stop getting messages from HospitalityHub login to your Supplier Panel <- OMA.Web> and remove yourself as a contact under settings.
22	Retrieve your login details <- OMA.Web>
23	<https://www.hospitalityhub.com.au/Newsservice/EnquiryTrack?enquiryTrackingId=> <https://www.google-analytics.com/co...plier_Email&ea=Open&el=HHQuoterequestsupplier> <http://url6421.industracom.com/wf/o...VZL7DlztsGY9WpEaxxzmlV9TdzSq3KwnzlpSJJCjpo-3D>
Table1

 

[JeffGrant]

Hi KerryX. Thank you for that. I didn't think of using PQ.. I am a real novice at PQ.....Is there anyway we can one step further and transpose this data into columns?

As much as I only pasted one cell, the reality is that when scrapping the emails, there could anywhere between 20 & 50 of these everyday. It is the CRM that requires one row per entry

 

[JeffGrant]

Hi KerryX, I worked out the Transpose Step. Thanks

 

[RoryA]

Doing Find & Replace wont work because VALUES does not appear in the F & R drop down list box.

What does that mean?

 

[JeffGrant]

Hi Rory, normally, when doing an F & R, we can search in Values instead of Formulas, but when I click into the cell that contain outlook body text, there is no option to F & R in values.

 

[JeffGrant]

Hi Kerry, I was too quick. My apologies for not giving enough data. Your PQ code work great on cell, but not on many. Here is a couple of rows of the scrapped data where the column I need to transpose into one row is column 4.

when I apply & amend your code for this data set, all rows in column 4 gets transposed into one row..I guess I need to do some form of GroupBy based on columns 1,2 or 3 ???

Italian Gelato Concepts Pty Limited has a new Commercial Ice Cream & Gelato Machine Quote Request	01-11-2023 0:11​	HospitalityHub	<Hospitality and Food Service Equipment - Get Quotes to Compare - HospitalityHub> A buyer has just requested a quote from Italian Gelato Concepts Pty Limited <Italian Gelato Concepts Pty Limited: Gelato & pastry manufacturing & display equipment - HospitalityHub Australia> on HospitalityHub. Buyer's Quote Request *Product Category: Commercial Ice Cream & Gelato Machine <Commercial Ice Cream & Gelato Machine in Australia for sale - Get Quotes to Compare Price & Specifications> *Requirements: I am looking for a soft serve ice cream machine *Where this will be used: Not for profit *Quantity: 1 *Needed in: Next few weeks Buyer Details *Name: Marley Sharp *Email: marleysharp111@gmail.com *Phone: 0475274989 *Company: Unspecified *Delivery Location: QLD *Postcode: 4895 How was this enquiry? <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0MTQ1JnN0YXRzPTE=> / <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0MTQ1JnN0YXRzPTA=> Manage your content, view leads and track activity Login <- OMA.Web> Australia's favourite hospitality marketplace Operated by Industracom <Industracom> To stop getting messages from HospitalityHub login to your Supplier Panel <- OMA.Web> and remove yourself as a contact under settings. Retrieve your login details <- OMA.Web> <https://www.hospitalityhub.com.au/Newsservice/EnquiryTrack?enquiryTrackingId=> <https://www.google-analytics.com/co...plier_Email&ea=Open&el=HHQuoterequestsupplier> <http://url6421.industracom.com/wf/o...GgXLJtX-2BrfBia8vJehO3AhjxmqsCpTcrtzC6GTY0-3D>
Italian Gelato Concepts Pty Limited has a new Soft Serve Ice Cream Machine Quote Request	01-11-2023 8:34​	HospitalityHub	<Hospitality and Food Service Equipment - Get Quotes to Compare - HospitalityHub> A buyer has just requested a quote from Italian Gelato Concepts Pty Limited <Italian Gelato Concepts Pty Limited: Gelato & pastry manufacturing & display equipment - HospitalityHub Australia> on HospitalityHub. Buyer's Quote Request *Product Category: Soft Serve Ice Cream Machine <http://hospitalityhub.com.au/buy/soft-serve-ice-cream-machine> *Requirements: I’m wanting a soft serve machine with a frozen yoghurt combo or something along them lines. *Where this will be used: Not for profit *Quantity: 1 *Needed in: Next few weeks Buyer Details *Name: Marley Sharp *Email: marleysharp111@gmail.com *Phone: 0475274989 *Company: Indigenous community *Delivery Location: QLD *Postcode: 4895 How was this enquiry? <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0MzQzJnN0YXRzPTE=> / <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0MzQzJnN0YXRzPTA=> Manage your content, view leads and track activity Login <- OMA.Web> Australia's favourite hospitality marketplace Operated by Industracom <Industracom> To stop getting messages from HospitalityHub login to your Supplier Panel <- OMA.Web> and remove yourself as a contact under settings. Retrieve your login details <- OMA.Web> <https://www.hospitalityhub.com.au/Newsservice/EnquiryTrack?enquiryTrackingId=> <https://www.google-analytics.com/co...plier_Email&ea=Open&el=HHQuoterequestsupplier> <http://url6421.industracom.com/wf/o...VZL7DlztsGY9WpEaxxzmlV9TdzSq3KwnzlpSJJCjpo-3D>
Italian Gelato Concepts Pty Limited has a new Gelato & Ice Cream Display Freezer Quote Request	01-11-2023 12:57​	HospitalityHub	<Hospitality and Food Service Equipment - Get Quotes to Compare - HospitalityHub> A buyer sent this quote request to another supplier and has invited Italian Gelato Concepts Pty Limited <Italian Gelato Concepts Pty Limited: Gelato & pastry manufacturing & display equipment - HospitalityHub Australia> to quote on this or a similar product. Buyer's Quote Request *Product Category: Gelato & Ice Cream Display Freezer <http://hospitalityhub.com.au/buy/gelato-and-ice-cream-display-freezer> *Requirements: [This buyer has indicated they are open to different suppliers and brands] for my restaurant. inside a shop *Where this will be used: Commercial *Quantity: 1 *Needed in: Next few days Buyer Details *Name: sam fakhri *Email: sam@sureprojectsaustralia.com.au *Phone: 0405976417 *Company: sureprojects australia *Delivery Location: NSW *Postcode: 2146 How was this enquiry? <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0NzczJnN0YXRzPTE=> / <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0NzczJnN0YXRzPTA=> Manage your content, view leads and track activity Login <- OMA.Web> Australia's favourite hospitality marketplace Operated by Industracom <Industracom> To stop getting messages from HospitalityHub login to your Supplier Panel <- OMA.Web> and remove yourself as a contact under settings. Retrieve your login details <- OMA.Web> <https://www.hospitalityhub.com.au/Newsservice/EnquiryTrack?enquiryTrackingId=> <https://www.google-analytics.com/co...plier_Email&ea=Open&el=HHQuoterequestsupplier> <http://url6421.industracom.com/wf/o...SkEK8qFajbVOhmaadRkq0dNj4i63Wy-2Fat673M02g-3D>
Italian Gelato Concepts Pty Limited has a new Gelato & Ice Cream Display Freezer Quote Request	01-11-2023 12:43​	HospitalityHub	<Hospitality and Food Service Equipment - Get Quotes to Compare - HospitalityHub> A buyer sent this quote request to another supplier and has invited Italian Gelato Concepts Pty Limited <Italian Gelato Concepts Pty Limited: Gelato & pastry manufacturing & display equipment - HospitalityHub Australia> to quote on this or a similar product. Buyer's Quote Request *Product Category: Gelato & Ice Cream Display Freezer <http://hospitalityhub.com.au/buy/gelato-and-ice-cream-display-freezer> *Requirements: [This buyer has indicated they are open to different suppliers and brands] Looking for a countertop ice cream freezer for a food truck. *Where this will be used: Commercial *Quantity: 1 *Needed in: Next few weeks Buyer Details *Name: Tony Overall *Email: tony@foodpuzzle.com.au *Phone: 0407599998 *Company: Cool Bananas *Delivery Location: NSW *Postcode: 2026 How was this enquiry? <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0NjMxJnN0YXRzPTE=> / <http://www.hospitalityhub.com.au/getquotes/survey?k=aWQ9NzU0NjMxJnN0YXRzPTA=> Manage your content, view leads and track activity Login <- OMA.Web> Australia's favourite hospitality marketplace Operated by Industracom <Industracom> To stop getting messages from HospitalityHub login to your Supplier Panel <- OMA.Web> and remove yourself as a contact under settings. Retrieve your login details <- OMA.Web> <https://www.hospitalityhub.com.au/Newsservice/EnquiryTrack?enquiryTrackingId=> <https://www.google-analytics.com/co...plier_Email&ea=Open&el=HHQuoterequestsupplier> <http://url6421.industracom.com/wf/o...aMLS4SXg8-2F6vqzjv2762sy17CO6rB9tgvpv17wgs-3D>

 

[JeffGrant]

This is what my data looks like.

 

[RoryA]

normally, when doing an F & R, we can search in Values instead of Formulas

No, you can't. You can with a Find, but not a replace, because you cannot replace part of the result of a formula, which is what Values would imply.

Since for a cell without a real formula, the value is also the formula, it works just fine to replace things in static values.

 

[Kerryx]

Ok hopefully this should sort you out.
First thing i did was add a new column to source , basically to identify each email as unique numbers 1....2.. see new Column 1

to give this output

Book1
	A	B	C	D	E
1	Column1	1	2	3	4
2	1	<Hospitality and Food Service Equipment - Get Quotes to Compare - HospitalityHub>	A buyer has just requested a quote from Italian Gelato Concepts Pty Limited <Italian Gelato Concepts Pty Limited: Gelato & pastry manufacturing & display equipment - HospitalityHub Australia> on HospitalityHub.	Buyer's Quote Request	* Product Category: Soft Serve Ice Cream Machine <Soft Serve Machine & Frozen Yoghurt Machine in Australia for sale - Get Quotes to Compare Price & Specifications>
3	2	<Hospitality and Food Service Equipment - Get Quotes to Compare - HospitalityHub>	A buyer has just requested a quote from Italian Gelato Concepts Pty Limited <Italian Gelato Concepts Pty Limited: Gelato & pastry manufacturing & display equipment - HospitalityHub Australia> on HospitalityHub.	Buyer's Quote Request	* Product Category: Soft Serve Ice Cream Machine <Soft Serve Machine & Frozen Yoghurt Machine in Australia for sale - Get Quotes to Compare Price & Specifications>
Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Split", each Text.Split([Column2],"#(lf)")),
    #"Expanded Split" = Table.ExpandListColumn(#"Added Custom", "Split"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Split",{"Column2"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Split] <> "")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Column1"}, {{"Group", each _, type table [Column1=number, Split=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Group],"Index",1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Split", "Index"}, {"Column1", "Split", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-IE"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-IE")[Index]), "Index", "Split")
in
    #"Pivoted Column"