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

[Guest]

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!

 

[Alan]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Alan]

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.

 

[Guest]

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.

 

[Alan]

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.