First a couple of warnings ...
1. We don't normally just produce code unless the OP has tried something for themselves
2. You're lucky I got intrigued by this, but I haven't made it particularly tidy - there are better ways of doing some bit and you will have to sort out the different address formats for yourself.
3. I wouldn't have done this in VBA - but I've assumed that's the requirement. This solution will port across to VB.NET without too many issues.
How I approached this...
Quote:
the body of the email is contained within one cell
- OK, that means I can just treat it as a string and split it across the lines.
Then I hit my first problem - the same one you did
Quote:
but there are no clear line breaks that I'm able to utilize
So I utilised the immediate window in Excel VBA and typed
? asc(mid$(s, 46,1))
which gave me the answer
10
. That means I can treat each cell as a string and split it using
chr$(10)
to give me the body of the text as an array of strings.
Dim s As String
s = Range("A1").Text
Dim lines() As String
lines = Split(s, Chr$(10))
Then I took what I knew about the text based on observation. I knew I was going to be interested in specific words or phrases, so I put the text into some constants ...
Const order_no = "Order Number : "
Const bill_to = "Bill To:"
Const shipping = "Shipping: Price+:"
Const salestax = "Sales Tax:"
Const total = "Total:"
Const headers = "Quantity Price/Ea."
Const cpName = "Name"
Const cpQuant = "Quantity"
Const cpPrice = "Price/Ea."
Const cpTotal = "Total"
Also from observation I noticed that I was going to need to know not just an overview of what the line contained, but also in which position (column) certain things could be found, so
Dim bill_to_pos As Integer
Dim pCode As Integer
Dim pName As Integer
Dim pQuant As Integer
Dim pPrice As Integer
Dim pTotal As Integer
Then it was a simple case of moving through the array, determining what, if anything, I could get out of each line, and extracting the data I needed using string functions.
Here's the entire function ... not elegant but it works
Option Explicit
Sub Test()
Const order_no = "Order Number : "
Const bill_to = "Bill To:"
Const shipping = "Shipping: Price+:"
Const salestax = "Sales Tax:"
Const total = "Total:"
Const headers = "Quantity Price/Ea."
Const cpName = "Name"
Const cpQuant = "Quantity"
Const cpPrice = "Price/Ea."
Const cpTotal = "Total"
Dim bill_to_pos As Integer
Dim pCode As Integer
Dim pName As Integer
Dim pQuant As Integer
Dim pPrice As Integer
Dim pTotal As Integer
Dim order_number As String
Dim ship_to_name As String
Dim bill_to_name As String
Dim ship_to_email As String
Dim bill_to_email As String
Dim ship_to_phone As String
Dim bill_to_phone As String
Dim ship_to_addr1 As String
Dim bill_to_addr1 As String
Dim ship_to_addr2 As String
Dim bill_to_addr2 As String
Dim ship_to_addr3 As String
Dim bill_to_addr3 As String
Dim shipcost As String
Dim taxcost As String
Dim totalcost As String
Const MaxNoItems = 5
Dim bInList As Boolean
Dim Codes(MaxNoItems) As String
Dim ItemNames(MaxNoItems) As String
Dim Quants(MaxNoItems) As String
Dim Prices(MaxNoItems) As String
Dim Totals(MaxNoItems) As String
Dim NoOfItems As Integer
Dim s As String
s = Range("A1").Text
Dim lines() As String
lines = Split(s, Chr$(10))
bInList = False
NoOfItems = 0
Dim i As Integer
Dim p As Integer
For i = 0 To UBound(lines)
p = InStr(lines(i), order_no)
If p > 0 Then
order_number = Mid$(lines(i), p + Len(order_no))
End If
p = InStr(lines(i), bill_to)
If p > 0 Then
bill_to_pos = p
i = i + 1
ship_to_name = Mid$(lines(i), 1, bill_to_pos - 1)
bill_to_name = Mid$(lines(i), bill_to_pos)
i = i + 1
ship_to_email = Mid$(lines(i), 1, bill_to_pos - 1)
bill_to_email = Mid$(lines(i), bill_to_pos)
i = i + 1
ship_to_phone = Mid$(lines(i), 1, bill_to_pos - 1)
bill_to_phone = Mid$(lines(i), bill_to_pos)
i = i + 3
ship_to_addr1 = Mid$(lines(i), 1, bill_to_pos - 1)
bill_to_addr1 = Mid$(lines(i), bill_to_pos)
i = i + 1
ship_to_addr2 = Mid$(lines(i), 1, bill_to_pos - 1)
bill_to_addr2 = Mid$(lines(i), bill_to_pos)
i = i + 1
ship_to_addr3 = Mid$(lines(i), 1, bill_to_pos - 1)
bill_to_addr3 = Mid$(lines(i), bill_to_pos)
End If
p = InStr(lines(i), headers)
If p > 0 Then
pCode = 1
pName = InStr(lines(i), cpName)
pQuant = InStr(lines(i), cpQuant)
pPrice = InStr(lines(i), cpPrice)
pTotal = pPrice + Len(cpPrice)
bInList = True
i = i + 1
End If
p = InStr(lines(i), shipping)
If p > 0 Then
shipcost = Mid$(lines(i), p + Len(shipping))
bInList = False
End If
p = InStr(lines(i), salestax)
If p > 0 Then
taxcost = Mid$(lines(i), p + Len(salestax))
End If
p = InStr(lines(i), total)
If p > 0 Then
totalcost = Mid$(lines(i), p + Len(total))
End If
If bInList Then
If Trim$(lines(i)) <> "" And Left$(lines(i), 3) <> "---" Then
NoOfItems = NoOfItems + 1
Codes(NoOfItems) = Mid$(lines(i), pCode, pName - 1)
ItemNames(NoOfItems) = Mid$(lines(i), pName, pQuant - pName - 1)
Quants(NoOfItems) = Mid$(lines(i), pQuant, pPrice - pQuant - 1)
Prices(NoOfItems) = Mid$(lines(i), pPrice, pTotal - pPrice - 1)
Totals(NoOfItems) = Mid$(lines(i), pTotal)
End If
End If
Next
Debug.Print "Order Number : " + order_number
Debug.Print ""
Debug.Print "Ship to Name : " + ship_to_name
Debug.Print "Ship to email : " + ship_to_email
Debug.Print "Ship to Phone : " + ship_to_phone
Debug.Print "Ship to Addr1 : " + ship_to_addr1
Debug.Print "Ship to Addr2 : " + ship_to_addr2
Debug.Print "Ship to Addr3 : " + ship_to_addr3
Debug.Print ""
Debug.Print "Bill To Name : " + bill_to_name
Debug.Print "Bill to email : " + bill_to_email
Debug.Print "Bill to Phone : " + bill_to_phone
Debug.Print "Bill to Addr1 : " + bill_to_addr1
Debug.Print "Bill to Addr2 : " + bill_to_addr2
Debug.Print "Bill to Addr3 : " + bill_to_addr3
Debug.Print ""
Debug.Print "Shipping Cost : " + shipcost
Debug.Print "Sales Tax : " + taxcost
Debug.Print "Total Cost : " + totalcost
Debug.Print ""
Debug.Print "Number of items : " + CStr(NoOfItems)
For i = 1 To NoOfItems
Debug.Print "-- Code : " + Codes(NoOfItems)
Debug.Print "-- Name : " + ItemNames(NoOfItems)
Debug.Print "-- Price : " + Prices(NoOfItems)
Debug.Print "-- Total : " + Totals(NoOfItems)
Next
End Sub
And here is the output from all those Debug.Print statements at the end (Note I changed some of the numbers to prove I was picking up the correct text)
Order Number : 5
Ship to Name : FIRSTNAME LASTNAME
Ship to email : email@domain.com
Ship to Phone : 5559991111
Ship to Addr1 : 123 Address
Ship to Addr2 : City ST 55555
Ship to Addr3 : US
Bill To Name : FIRSTNAME LASTNAME
Bill to email : email@domain.com
Bill to Phone : 5559991111
Bill to Addr1 : 123 Address
Bill to Addr2 : Portland OR 97210
Bill to Addr3 : US
Shipping Cost : $6.95
Sales Tax : $0.00
Total Cost : $36.90
Number of items : 1
-- Code : V-BotAAS2
-- Name : Item Name
-- Price : $29.9
-- Total : $59.90
It's down to you to convert those strings into something useful.
Also watch out because you can't have macros in CSV files, you may have to convert to XLS or have the spreadsheet import the CSV via another macro