Outlook email text to Excel - I'm so close!

I used the outlook export wizard to pull some emails that I've had sent to Outlook via a sendmail.asp to an Excel format - quite sucessfully and easy too.  However, I also need to format the fields within the "body" of the email.  The "body" field appears as one column in the Excel spreadshet that is created and I thought I could use the 'text to columns' feature.  However, there's these boxes that appear in lieu of spaces that seem to messing up everything.  If I try to use the 'delimited' feature when I get to that screen - the data dissappears.  When I use the 'fixed width' feature the data remains but, the data isn't fixed width, ie: names, addresses, city, st, etc.   I think if I could get rid of the boxes that would be a start - I think they are LF or CR of some sort.  But when I try to a find and replace - it doesn't recognize the box that I copied.  I've also tried to use the boxes as 'delimiters' and again they aren't recognized.  Any ideas?  (Hopefully I've explained the steps well enough.  One other item - I tried pulling it into MS Access also, and in their the 'body' data dissappears altogether - thought I'd mention that too as a clue to whatever is going on.)  Thanks!
0
anonymous (74722)
5/24/2004 3:51:04 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
363 Views

Similar Articles

[PageSpeed] 33

"katchatu" <anonymous@discussions.microsoft.com> wrote in message
news:6ABEE09F-D5EB-415D-8863-825B0E2B476E@microsoft.com...
>
> I used the outlook export wizard to pull some emails that I've had
> sent to Outlook via a sendmail.asp to an Excel format - quite
> sucessfully and easy too.  However, I also need to format the fields
> within the "body" of the email.  The "body" field appears as one
> column in the Excel spreadshet that is created and I thought I could
> use the 'text to columns' feature.  However, there's these boxes
> that appear in lieu of spaces that seem to messing up everything.
> If I try to use the 'delimited' feature when I get to that screen -
> the data dissappears.  When I use the 'fixed width' feature the data
> remains but, the data isn't fixed width, ie: names, addresses, city,
> st, etc.   I think if I could get rid of the boxes that would be a
> start - I think they are LF or CR of some sort.  But when I try to a
> find and replace - it doesn't recognize the box that I copied.  I've
> also tried to use the boxes as 'delimiters' and again they aren't
> recognized.  Any ideas?  (Hopefully I've explained the steps well
> enough.  One other item - I tried pulling it into MS Access also,
> and in their the 'body' data dissappears altogether - thought I'd
> mention that too as a clue to whatever is going on.)  Thanks!
>

Hi katchatu,

I am assuming you have already made sure that excel is displaying in
the same font as the text was in originally?  If so:

I would suggest you try to work out what those box symbols represent.

If you find one, determine it's position in a string, and then use the
CODE function to determine its ASCII code number.

It will probably be a character that exists in the font imported, but
not in the font you have in Excel.

If you can determine what it is, you might then be able to remove or
replace it.

HTH,

Alan.






0
alan4377 (173)
5/24/2004 4:02:34 AM
Yes, it indicates that it is 13.  Now, how to find and replace this (ideally with a "," or ":')?  I also pulled the data into word and it shows the character as a paragraph symbol.
Thanks for replying.
0
anonymous (74722)
5/24/2004 5:06:03 AM
Well as unorthodoxed as it may be - here's what I found works.  I pull the data into excel, then copy the emails "body" column, and paste that into MS Word, in there I can do a find and replace on the symbol (turns into a LF symbol using this method) with a ":" , then I copy that and paste it back into Excel - then I'm off and running and can use the text to columns feature.  It may not be pretty, but it works :)
0
anonymous (74722)
5/24/2004 5:21:03 AM
"katchatu" <anonymous@discussions.microsoft.com> wrote in message
news:4EF36BDF-61BE-4F5E-B843-12DE23FF994E@microsoft.com...
>
> Yes, it indicates that it is 13.  Now, how to find and replace this
> (ideally with a "," or ":')?  I also pulled the data into word and
> it shows the character as a paragraph symbol. Thanks for replying.
>

Hi katchatu,

I think you will find that Chr(13) represents a line feed / carriage
return.

I guess you could do a search / replace in your code for that
character and replace it with, for example, a colon.

For example:

=SUBSTITUTE(A1,CHAR(13),":")

I am speculating that the text from outlook is coming through, and
where the lines wrap in outlook, they get sent with that character
even though, technically, they weren't in there in the original (the
aplication wrapped the text automatically).

Does that help?

Alan.




0
alan4377 (173)
5/24/2004 5:21:43 AM
afraid not.  Only converts some of the boxes and also, once again, when I try to indicate a delimitor(sp?), the data dissappears on that screen.  Oddly the data appears in the first screen when it wants to know the data type (fixed or delimited).  Thanks again for your help.
0
anonymous (74722)
5/24/2004 5:51:03 AM
"katchatu" <anonymous@discussions.microsoft.com> wrote in message
news:22DCB018-7CF7-4B63-8440-01767E1D7565@microsoft.com...
>
> afraid not.  Only converts some of the boxes and also, once again,
> when I try to indicate a delimitor(sp?), the data dissappears on
> that screen.  Oddly the data appears in the first screen when it
> wants to know the data type (fixed or delimited).  Thanks again for
> your help
>

That implies to me that some, but not all, of the boxes are Chr(13).

Perhaps you might try this:

1)  Import the data to excel in a single column

2)  Do the substitution as outlined above.

3)  Check what CODE any of the the remaining boxes have

4)  Amend the substitution formulae to cover the additional CODE

     For example:

     =SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),":"),CHAR(10),":")

5)  Repeat 3 and 4 as many times as necessary (you may have multiple
     nested formulae)

6) Save the results for next time (should only be a one of thing to
     find them all).


HTH,

Alan.






0
alan4377 (173)
5/24/2004 6:04:04 AM
Reply:

Similar Artilces:

Text Values
Hello, Can anybody help, I'm after making a spreadsheet in Excel to record times for individuals, for example if I typed in 'early shift' with the value of 10 hours, after 'noon shift' 8 hours as well as 'late' shift at 12 hours...etc, the total values would all show in a totals cell for that person. I would appreciate any help with the above. Love, Susan ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** Hi Susan one way: use a helper column which transforms this text string int...

Excel button problem
Hi All I have a macro that copies a worksheet in the active workbook and puts it into a new workbook - then formats it and deletes any buttons on the worksheet. On the first click on the button the macro works ok. On the second click, it fails because the all assigned macros on all buttons in the active workbook changed from "mba" to "book1!mba". Book 1 doesn't exists (wasn't opened, wasn't saved, doesn't have the macros). I've never experienced this problem before?? Can anyone help to solve this problem? FYI The macro to do this is c...

Sorting emails by domains, from org to edu (right char is the most significant)
Hello All I need to sort the domains according their emails. For example: Before sorting: john1@abc.edu john3@abc.org john5@abc.com john4@bcd.org john2@bcd.edu john6@bcd.com After sorting: john3@abc.org john4@bcd.org john5@abc.com john6@bcd.com john1@abc.edu john2@bcd.edu That is, how to sort, according to the domain name ( the right is the most significant )? Thanks. Z. D. On Feb 15, 11:09 pm, "duzhid...@gmail.com" <duzhid...@gmail.com> wrote: > That is, how to sort, according to the domain name ( the right is the > most significant )? you'll probably need ...

Excel 97 #9
Please can anyone help??? I have two columns in Excel 97. The first contains a list of statu values eg. pending, or granted or withdrawn. The second contains date eg.01/12/1997, 05/06/2003. I woudl like to know how to get all th granted apps before 31/12/2003. Can anyone help please -- Message posted from http://www.ExcelForum.com theres many ways, but an easiest way would be to do a sort. Highlight the 2 columns, click on data, then sort, then sort by status, then by date. this should group them all together. hope this helps...toe >-----Original Message----- >Please can anyo...

Moving from Outlook Express to Outlook
How do you transfer files from OE to Outlook - inbox, contacts, etc. Can you do so if you have already started using Outlook but want to bring these files over. cooker <anonymous@discussions.microsoft.com> wrote: > How do you transfer files from OE to Outlook - inbox, contacts, etc. > Can you do so if you have already started using Outlook but want to > bring these files over. Start Outlook Express, click File>Export>Messages. If you have Outlook running at the same time, you'll see the messages appear in Outlook. -- Brian Tillman Smiths Aerospace 3290 Patterson ...

invalid email address
In message to Candy FH Muffman wrote of having an invalid email address. I use hotmail but would like to prevent ti being used by spammers. Is there any way I can hide it or restrict it in some way? Can I make it invalid? Thank you You mean when posting to an online service like this? Sure, don't type your (correct) email address. See my address or from many others to see an example. Note: I've removed your valid address in my reply. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add...

From Outlook 2000 to Outllook 2003
How do I migrate I personal folders file (.pst) from Outlook 2000 to Outlook 2003? Read the Help Files: http://office.microsoft.com/en-us/assistance/HA010771141033.aspx -- Russ Valentine [MVP-Outlook] "rolo" <rolo@discussions.microsoft.com> wrote in message news:706405A0-2971-409F-B213-67714B12713C@microsoft.com... > How do I migrate I personal folders file (.pst) from Outlook 2000 to > Outlook > 2003? Thanks Russ it helped. By the way how can I get to this useful help files? "Russ Valentine [MVP-Outlook]" wrote: > Read the Help Files: > htt...

how to convert lookup values to the "display text"
I'm using an sql code (below) which uses a few lookup fields. Unfortunately in the datasheet view, I get the "bound values" instead of the "display values". How can I change the properties for the these lookup fields so I can see the "display values" from the datasheet view? SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [Form_9_Status] UNION ALL SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [TDY_Status] UNION ALL SELECT [Funding],[Date],[Description],[C...

Recieving email with nothing in them? Blank??
From time to time, I will recieve email from friends, family, or business...and when I get the email, I open it up to find nothing but white space. I can't find a common thing between emails. I just know it is very frustrating when I have to ask people to resend the email to me or to a new address. When I look at my mail on the server, it is fine. It is when I open Outlook and it downloads it to my desktop. I get nothing. Any ideas? Cheers.. vg Sorry, wanted to add that I am using OL2003 with WINXP and everything is updated SP wise. I am starting to see some articles on this...

Outlook 0x800ccc0d error when Norton e-mail protect enabled: see hosts
This post is made to help others solve this issue, based on my experience. Symptom: - Outlook works perfectly well when Norton Anti-Virus e-mail protection is disabled - Outlook cannot retrieve incoming messages when NAV e-mail protection is enabled, message being: pop3 server not found, error 0x800ccc0d This symptom, and possible solutions, are exactly the object of Symantec support note: http://service1.symantec.com/SUPPORT/nav.nsf/docid/2000020716064206 Please read this note first ! The object of this post is to add another possible solution to this problem. NAV email protection sets up...

Attaching Contacts to new email
Creating a new email. When contacts folder has "shared" contacts and "personal" contacts how can you set your personal contacts as the default? Example: creating a new email having never addressed the "send to" contact before, you hit the "To" button. Currently my "shared" contacts opens up but I would like my "personal" contacts page to open instead of having to drop down to "contacts" to bring up that list. Is there a solution to this? Thank you!!! On 2/26/2010 10:21 AM, assistantneedshelp wrote: >...

Some Emails Cannot be Delivered
I have a problem that I cannot put a handle on with my exchange server. Some outbound messages do not reach their destination. The problem happens with certain destinations. However the confusing part is that some messages are able to make it through. This would mean that there are no problems with DNS or MX lookup or any connectivity issue since some emails manage to make it through. I'm at a loss and can't figure where to look first I hope that someone might have an idea. You'll have to provide more information. Are you receiving NDrs if so can you share them? Is ther...

Looking for Excel Help
I'm a very novice Excel user and am looking for a little help with creating a formula for a spreadsheet I'm creating for my personal use. I would appreciate some assistance if possible. Thanks in advance. Dan --- Message posted from http://www.ExcelForum.com/ Hi Dan! Post a sample of what you want to do. Your question is just a tad open ended <g> -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "DanB4105" <DanB4105.ywtpa@excelfor...

New to excel
Hi All, I'm new to Excel ( and to this forum :) ) and so I hope somebody may b able to help me. I've got 2 questions.... QUESTION 1 I've got a spreadsheet which takes data from one worksheet and uses i to calculate data in a second worksheet using the following code formula: =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th Novembe 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4t November 2005'!B19,"DOWN")))) The problem is, when I create a new worksheet I have...

setup Windows Mail as Word 2003 default emailer
All I can do is setup Outlook. I do not use Outlook. I would like to email Word docs using MS Windows Mail (new version of Express) In the Windows Start area, type Regedit into the search bar and then start the Registry Editor and go to HKEY_CURRENT_USER>Software>Clients>Mail Right Click on the (Default) item and then on Modify and in the Value data: field enter Windows Mail so that after you click OK, you have (Default) REG_SZ WIndows Mail -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a pa...

Outlook Sales
I have installed the CRM Server,Outlook Router, SQL, the whole nine yards so to say. So now when I try to install the Sales for Outlook CD, it gets to "Initializing" and then crashes with an error message that is unknown. Here is the message; Product: Microsoft CRM Sales for Outlook -- Installation operation failed. The Event ID is 11708, any help would be appreciated. It is running on WIN2000 Server with Dual Xeon 3.06 with 2GB of Ram. Thanks, Bobby Bobby You cannot install the outlook client on the same machine as the MSCRM server - that's probably why it's bomb...

Emails #3
Hi, I was wondering if anyone knew of any web based email provder that I could use that wont be bloked but the I.T Filer at my work. I require use of emails during the day for personal use but work emails are monitored. I have tired various sites i.e Hotmail, Yahoo, Gmail, lycos etc but they are all blocked. Does anyone know of any that may not be picked up buy the web filer. Fiona Fiona, It is difficult to answer because it depends on what your filter is and how it is monitored. For instance, if it is actively monitored and somebody found out you were accessing the site, then it coul...

Excel corrupts when asking to update vlookups
We are experiencing weird behavior with some Office 2K3 Excel spreadsheets that contain lots of calculations, but no macros. On some pc’s Excel acts normally, on others you get the error. I have a couple of screen shots available. Any help is appreciated. 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. -- Don Gu...

Uninstall of mappoint has caused errors with excel
Hi, I am running Office 2003 on the terminal server (windows 2003) and had a copy of mappoint as well. This is a mapping program. We ininstalled mappoint which has caused an error message with Excel and other office products. The error says "Cd:\documents and settings\administrator.ocrdc1\application data\microsoft\addins c:\Program files\common files\microsoft shared\geography\mpoai9.dll is not a valid add-in." I then click OK and excel opens up and everything is fine. The problem is that we are using other programs as well such as Quickbooks that export to excel and t...

Outlook 2003 Drag and Drop Emails
I have an issue where there is a SBS 2003 server (newly installed) & when I drag emails to the file system (explorer window) in order to create file records of the emails it generates an error. Dialog Box Name: Error Copying File or Folder Error Msg: Not enough storage is available to process this command. I can't find an error logged anywhere, either on the server event logs or on the local machine event logs... I have searched the MS KB & Office online, but no joy yet... If anyone can help that would be great!!! R ...

outlook 97 and express email problems
Hi, I am currently on an IBM X21 laptop and is running windows 98 se with office 97 pro. I recently experienced some problems with outlook (illegal operations etc) and reinstalled office to fix the problem but since then I have not been able to send or recieve emails with outlook 97 and outlook express 6. I simply get an error message saying the host can't be found (but does exist and I can ping it successfully). Any suggestions on what I might do? I have tried creating new accounts in windows mail and outlook express, but I still get the same error. Thankyou in advance! Tim D...

Having problem with spoofing email
Our users just received multiple email from different users outside the company. In the To: line, it shows his user name correctly but when he print those email, the To: line was showing somebody else name on the print out. Is there a way to block this behavior? I'm using E2k3. For some reason our spam (postini) didn't pick up these emails. Thank you, Could you please post the message in raw format (including the mail headers) Petch wrote: > Our users just received multiple email from different users outside the > company. In the To: line, it shows his user name cor...

using the journal on outlook
Once I link an email to the journal, can I still find that email in my mail box? I seem to be able to get to it only via the journal. If this is the way it is supposed to be, how do I remove it from the journal and get it back into my mail box? Am I just missing something? -- thanks, Independent Are you linking to the item or putting a copy into the journal item? Also, has the item been archived or not? "Independent" <Independent@discussions.microsoft.com> wrote in message news:868279F2-53C8-403A-97F5-604CEECD873C@microsoft.com... > Once I link an email to the journ...

Stop Outlook from starting up automatically
Once I installed Outlook2003, it now runs automatically on startup. How do I disable this? -- | +-- Julian | are you using a PDA that is trying to access the data in it? If so, make sure the device is not connected when you boot. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook ne...

cannot open hyper links in outlook
when I try to open a hyperlink in outlook, I get the following message: This operation has been cancelled due to restrictions in effect on this computer. Please contact your system administrator. ----- I am the system administrator. HELP This is a problem with IE, not Outlook. You need to reset your internet settings in IE's Tools, Internet Options, Advanced tab. (Or Control Panel, Internet options, Advanced tab). See http://www.slipstick.com/problems/link_restrict.htm for more information. "Donald McNeely" <Donald McNeely@discussions.microsoft.com>...