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>

 

[JeffGrant]

Hi Kerry,

thanks for that.

Are the unique numbers added automatically like an index or are they manually added. If manually added, II will have VB add them as part of the email extraction process.

 

[Kerryx]

The numbers need to be added to source data before importing to Power query , this is identify each email as unique so we can group and set up the index grouping correctly in PQ, if there is a unique identifier field already in your source data that will allow this you are sorted.
In excel i just manually added 1 and 2 in the example so when pulled into PQ you will have this

So in PQ we can see last line of first email is 22 and the index resets to 1 again for first line of the next

We use the Index to manage the Pivot of the data to rows and columns

 

[Kerryx]

They don't need to be added to source , If you want you can it in PQ as a second step and call the new Column Column1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "Column1"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"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"