Pasting from Email to Excel

[Jaums]

I'm trying to find a way to be able to pick up notices I receive via email (Outlook Express) and paste them into an Excel spreadsheet. The portion of the email I need to capture looks like this:

108-CC3 Name of Item 29.95 1 29.95 Media_Type2

When pasting into Excel, everything goes into one cell rather than the item # in the first, Name of item in the second, etc. I've tried all choices under Paste Special. If I paste into Word, the spaces between the items are little degree symbols (little oo as superscript). [Can you tell I know nothing about HTML?-->at least I think that's what that is.] The best thing I have found is to paste it into Word, manually change all of the oo guys to tabs, then copy and paste into Excel. Not much fun.

I will be getting these notices regularly and don't want to keep all the emails; would like to be able to grab each one and put it in the spreadsheet and total certain columns, etc. I also have MS Works, but haven't found anything there to help.

Thanks!

Jaums

[Paul Komski]

It might help to see the source code. Select a mail and From File<>Properties<>Details<>MessageSource copy and paste it here (edit out anything private) and see if anyone can decrypt the reason.

Someone else may know directly what the oo-separated values relate to of course.

Will it work from Word by replacing with commas instead of tabs? Either way if this is the only way to do it you could create a macro to make that part of the process much more painless.

[Jaums]

OK I pasted as you suggested. I replaced personal stuff with xxx. The part I want to capture is the last line starting after the word 'attributes' and continuing to the end. The oo guys appear between each of the items after the attributes.

Replacing with tabs works, it's just tedious. Excel intreprets the tabs as 'move to the next cell'. But, I'd rather be able to just paste, or paste into Word, use Replace to replace all of the oo things with tabs, but I don't know how to tell Replace what they are. Then paste into Excel. Maybe it would be better to just do old fashioned 'data input'--->type the stuff in. It's not much typing, but not cool at all to do it that way.


return-Path: <xxx@xxx.com>
Received: from xxx.xxx.com ([xx.xx.xxx.xx]) by xxx.xxx.net
(InterMail xx.x.xx.xx.xx xxx-xxx-xxx-xxx-xxx-xxxxxxxx) with SMTP
id <20021129xxxxxx.HMEKxxxx.xxxx.xxxxxxx.net@xxxxx.xxx xxxxxxxx.com>
for <xxxxxxxx@xxxxxxx.net>; Fri, 29 Nov 2002 0x:xx:xx -xxxx
Received: (xxxxx xxxxx invoked by uid xxx); 29 Nov 2002 xx:xx:xx -0000
Received: from xxxxxxx@xxxxxxxxxxx.com by t-rex by uid xxx with xxx-scanner-x.xx (. Clear:. Processed in 0.0987130000000001 secs); 29 Nov 2002 xx:xx:xx -0000
X-xxxxx-Scanner-Mail-From: xxxxxxx@xxxxoxxxxxxxxxx.com via x-xxx
X-xxxxx-Scanner: x.xx (Clear:. Processed in 0.0987130000000001 secs)
Received: from unknown (HELO www.xxxxxxxxxxxxxx.com) (xx.xxx.xxx.xx)
by xxx.xxxxxxxxxxx.com with SMTP; 29 Nov 2002 xx:xx:xx -0000
Date: Fri, 29 Nov 2002 xx:xx:xx -0500
From: <xxxxx@xxxxxxxxxxxxxxxx.com>
To: <xx.xxx@xxxxxxx.net>
X-Mailer: Miva v3.9408
Subject: xxxxx xxx xxxxxxxx xxxx x xxx xxxxxx
Message-Id: <20021129xxxxxx.xxxxxxxxx.xxxxxx.xxxxxx.net@xxxxx.x xxxxx.com>


xxxxxxxxxxxxxxx xxxxxxx xxxxx xxx xxxxxxxx xxxx xxx xxxxxx x xxx xxxxxxx xxx. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxx xxxxxxxxxxxxxxxx

xxxfxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxx
xxxxx xxxxxx xxxx xx xxxxxxxxx
order ID: xxxx
Placed:11/29/2002

Code Name Price Quantity Total Attributes
xxx-xxx xxxxxxxx xxxx x: xxxxx 29.95 1 29.95 Media_Type2 :DVD8.00

[Paul Komski]

First thing is to find out what these characters are, even though they don't show in what you posted.

Do they look like ° or º for example.

These happen to be the extended characters 176 and 186 respectively.

Paste one of them (from your eMail) on its own into Excel Cell A1 and then paste =CODE(A1) into another cell and click the tick sign to retrieve/see its code value.

If you paste a string into A1, the code will be returned for only the first letter in the string. You could also use the the two I used in this message as controls. Don't include a space before the character or string or else you will have the code 32 returned (the code for a space of course) ;)

First ensure numlock is on and use the keypad when entering numbers.
(A)From Tools start a new macro and go through the actions of:-
1) Place the cursor at the start of the document.
2) From the Edit menu select replace
3) Put the cursor in the "find what" box and press ALT+0186 (or your actual code preceded by zero(s) to make it four digits in length) then release ALT; do this a second time.
4) Type ^t in the "replace with" box
5) Click "Replace All"
6) Close the Find/Replace Dialog Box
(B) Stop the Macro.

Now you should be able to just go to this macro (whatever you called it) and run it whenever you want.

[Jaums]

I'm trying to do this:

Paste one of them (from your eMail) on its own into Excel Cell A1 and then paste =CODE(A1) into another cell and click the tick sign to retrieve/see its code value.

First, what is the tick sign?

[I just noticed that in Word, if I select Hide, you can't see them. I'ts like a space or other delimiter, but there are spaces also.]

I pasted one in A1 from the email which immediately extends the width of A1 through Column P. As soon as I paste the =CODE (A1) in another cell, in A1 I get AD HTML 4.0 Transitional//EN"> and an A in A2, also extended through Column P (and a tilde or something over both As).

They do look like your 176. But if I use Find, alt + 0176 it only finds the one I put in using alt + 0176. It doesn't find the real ones. If I could figure out what it is, I could probably import into Excel from WordPad, using this character as the delimiter, except there is always more than one of them: two, except after Type 2 where there are 8 of them. And each one is a character - you need to hit the arrow key 2 times to move through the pairs of them; 8 arrow keys where there are 8 of them.

It's looking more and more like manual data input . . ..

[Paul Komski]

They appear to be two of the special characters called "non-breaking spaces" in MSWord.

if
tab = ^t

then
non-breaking space = ^s

ShortcutKeys for non-breaking space = CTL+Shift+Space

You also can insert them in word from Insert<>Symbol on the SpecialCharacter Tab.

The Tick! When you edit a cell, both a green tick and a red cross appear next to the = sign in the editing box at the top.

Hope you can solve it from here on in! :)

[Paul Komski]

Some Google Groups (http://groups.google.com/groups?q=non-breaking+space&sourceid=opera&num=0&ie=utf-8&oe=utf-8) for further info if you're interested.

It's usually also seen as a space in the character map (depending on the font). Its code is ALT+0160, where a normal space would be ALT+0032.

[papertech]

Did you try the following approach?

"Save as" the email file as a .txt file. Then open your excel program and choose "file/open" (file type must be "All files *.*"). This will access excel's import wizard where you can manipulate the spacing of the columns so the file will open properly. Afterwards, be sure to re-save your spreadsheet as an excel file.

Hope this helps.

[Paul Komski]

Cool papertech! Worked for me by choosing other delimiter and then ALT+0160 in the Other box and also checked "Treat Consecutive Delimiters as One".

But of course only Jaums has the original eMails! ;)