Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming,microsoft.public.outlook,microsoft.public.outlook.Program_VBA
|
|||
|
|||
Please help me Parse this
I have a email that comes from my supplier.
The email looks like this: C U S T O M E R R E C E I P T Supplier Order Number: 175382 America's Premier Value Merchant Order Date: 4/4/2004 1384 Commercial Ship Via: FedEx Huntington, IN 46759 Payment: MC 800-348-1239 Fax: 418-326-1806 Bill To: Ship To: Steve Jones Dusty Riley 5008 South Powell Ave 5421 NE 50th Street Blue Springs, MO 64015 Kansas City, MO 64119 Telephone: (816)229-7386 E-Mail: How can I extract the Order number, Order Date, Ship to Name and Address. Steve J |
#2
Posted to microsoft.public.excel.programming,microsoft.public.outlook,microsoft.public.outlook.Program_VBA
|
|||
|
|||
Please help me Parse this
You might want to post this to an Outlook programming group as it will
require some code to extract the information you wish. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, sjones asked: | I have a email that comes from my supplier. | | The email looks like this: | | C U S T O M E R R E C E I P T | | Supplier Order Number: 175382 | America's Premier Value Merchant Order Date: 4/4/2004 | 1384 Commercial Ship Via: | FedEx Huntington, IN 46759 Payment: MC | 800-348-1239 | Fax: 418-326-1806 | | | | Bill To: Ship To: | Steve Jones Dusty Riley | 5008 South Powell Ave 5421 NE 50th Street | | Blue Springs, MO 64015 Kansas City, MO 64119 | Telephone: (816)229-7386 | E-Mail: | | | How can I extract the Order number, Order Date, Ship to Name and | Address. | | | Steve J |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me Parse this
Regular Expressions does a very good job of this.
Although I'm so-so at Regular Expressions, I'm not familiar with the VBScript Regular Expressions library, so I couldn't give you examples. Here's an approach using Instr matching - it's pretty rough but should do the job. Assumes your billing and shipping address lines are separated by at least 3 spaces. Also assumes that there are no further sections after address that are separated by two columns using 3 spaces. Sub test() Dim str As String, i As Long Dim strOrderNumber As String, strOrderDate As String Dim strShipToName As String, strShipToAddress As String str = _ " C U S T O M E R R E C E I P T" & vbNewLine & _ vbNewLine & _ "Supplier Order Number: 175382" & vbNewLine & _ "America's Premier Value Merchant Order Date: 4/4/2004" & vbNewLine & _ "1384 Commercial Ship Via: FedEx" & vbNewLine & _ "Huntington, IN 46759 Payment: MC" & vbNewLine & _ "800-348-1239" & vbNewLine & _ "Fax: 418-326-1806" & vbNewLine & _ vbNewLine & _ vbNewLine & _ vbNewLine & _ " Bill To: Ship To:" & vbNewLine & _ "Steve Jones Dusty Riley" & vbNewLine & _ "5008 South Powell Ave 5421 NE 50th Street" & vbNewLine & _ vbNewLine & _ "Blue Springs, MO 64015 Kansas City, MO 64119" & vbNewLine & _ "Telephone: (816)229-7386" & vbNewLine & _ "E-Mail: dafella<atswbell.net" & vbNewLine On Error Resume Next i = InStr(1, str, "Order Number: ") + Len("Order Number: ") strOrderNumber = Mid(str, i, InStr(i, str, vbCr) - i) i = InStr(1, str, "Order Date: ") + Len("Order Date: ") strOrderDate = Mid(str, i, InStr(i, str, vbCr) - i) i = InStr(1, str, "Ship To:") i = InStr(i, str, " ") strShipToName = Trim(Mid(str, i, InStr(i, str, vbCr) - i)) i = InStr(i, str, vbCr) i = InStr(i, str, " ") strShipToAddress = Trim(Mid(str, i, InStr(i, str, vbCr) - i)) i = InStr(i, str, vbCr) i = InStr(i, str, " ") strShipToAddress = strShipToAddress & vbNewLine & Trim(Mid(str, i, InStr(i, str, vbCr) - i)) i = InStr(i, str, vbCr) i = InStr(i, str, " ") strShipToAddress = strShipToAddress & vbNewLine & Trim(Mid(str, i, InStr(i, str, vbCr) - i)) End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "sjones" wrote in message om... I have a email that comes from my supplier. The email looks like this: C U S T O M E R R E C E I P T Supplier Order Number: 175382 America's Premier Value Merchant Order Date: 4/4/2004 1384 Commercial Ship Via: FedEx Huntington, IN 46759 Payment: MC 800-348-1239 Fax: 418-326-1806 Bill To: Ship To: Steve Jones Dusty Riley 5008 South Powell Ave 5421 NE 50th Street Blue Springs, MO 64015 Kansas City, MO 64119 Telephone: (816)229-7386 E-Mail: How can I extract the Order number, Order Date, Ship to Name and Address. Steve J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Parse Name | Excel Discussion (Misc queries) | |||
Parse from the Right | Excel Worksheet Functions | |||
How do I parse columns? | Excel Worksheet Functions | |||
Parse csv files | Excel Discussion (Misc queries) | |||
Parse | Excel Programming |