Download G-Mail to Excel

[Janet]

I purchased a program to download our G-Mail inbox. We use tis G-Mail account
for a contest we have going on and there are many entries. The program works
pretty good, BUT it brings everything that is in the body, into one group of
cells. See below.
These are the four columns it brings into Excel. From, Subject, Received
Date, and Body - but it's the body of the email that I really need split into
separate columns
If someone could help me with a way to break this info out into separate
columns - I can send a sample of the whole spreadsheet if needed to.

User_name = Scott Dailey
title = Mechanical Engineer
company = AIM Machinery
State = IL
country = United States
User_email = (e-mail address removed)
Submit = Submit

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Chip Pearson]

I assume that everything from "User_Name" to "Submit" is the body of
the email and all of that text is placed in a single cell. The easiest
way to split it apart is with a function written in VBA.

Open the VBA editor (ALT F11 to open), select your workbook in the
Project window on the left side of the screen (CTRL R to open), and
insert a Module (Insert menu). In the module that is created, paste in
the following code:

Function SplitBodyFields(Body As String) As String()
Dim SS() As String
Dim N As Long
Dim M As Long
Dim FF() As String
Dim S As String
Dim Arr() As String
S = Body

N = InStr(1, S, Space(2))
Do Until N = 0
S = Replace(S, Space(2), Space(1))
N = InStr(1, S, Space(2))
Loop

SS = Split(S, Chr(10))
ReDim Arr(LBound(SS) To UBound(SS))
For N = LBound(SS) To UBound(SS)
FF = Split(SS(N), "=")
Arr(N) = Trim(FF(1))
Next N

SplitBodyFields = Arr
End Function

Now, close the VBA editor and return to Excel. Suppose that the body
of the email is in cell A10. Select cells B10:H10 (7 columns since
there are 7 items in the body) and type

=SplitBodyFields(A10)

and press CTRL SHIFT ENTER rather than just ENTER. This will return
the values (each on the right of the '=' character) into cells
B10:H10.

This is an array formula, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Janet]

Chip - no everything in the body is not in a single cell -- let me see if I
can paste an example here, no I tried and it just wraps too much to be seen
properly here,
Can I send you an example of what gets downloaded to excel?