import email addresses into an excell list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Big John - 19 Dec 2005 03:21 GMT

I have hundreds of email addresses in Word format. I would like to import
them into Excel and have them appear on different rows. When I copy and
paste all the new additions go to one row. When I import a special object it
stays in a box that floats.

Reply to this Message

David McRitchie - 19 Dec 2005 03:58 GMT

Not familiar with the effect you describe, but one thing I would try
would be to use Pure Text to convert the data in the clipboard to
plain text and then paste that. Even if it doesn't help you to this
problem -- it is a utility that you will use often.

PureText,2.0, Steve P.Miller, paste copied text as simple text into another
application without getting all the formatting from the original source
http://www.stevemiller.net/puretext/

it will remove hyperlinks, and remove all cell formatting leaving only text,
it will preserve new lines, so it should paste to separate rows.
if the original data was in separate rows.

Signature

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> I have hundreds of email addresses in Word format. I would like to import
> them into Excel and have them appear on different rows. When I copy and
> paste all the new additions go to one row. When I import a special object it
> stays in a box that floats.

Reply to this Message

Big John - 19 Dec 2005 05:24 GMT

Thanks very much for the help. It was hidden formatting that was the
problem. I had solved the problem by going through my list and hitting enter
after every address. There were hidden spaces and hidden open lines. Once I
had the list sanitized, it went right in. I am going to try pure text,
because it sounds like it would do the work for you. Thanks again.

Reply to this Message

David McRitchie - 19 Dec 2005 18:21 GMT

Hi John,
For the problem you just described, TrimALL macro would
have solve your problem -- didn't sound like same problem when
you first described it.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

You might have to get more information on what to remove or change with the
newlines problem. Excel only uses CHAR(10) so you would have
to figure out what to to with CHAR(13). (CRLF is x'0D0A)

Don't just remove them as you would at least need a space, you might do
something in a new subroutine (different name) that incorporates everyting in
the TrimALL macro plus the following::

Selection.Replace What:=Chr(13)&Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13, Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> > I have hundreds of email addresses in Word format. I would like to import
> > them into Excel and have them appear on different rows. When I copy and
[quoted text clipped - 6 lines]
> had the list sanitized, it went right in. I am going to try pure text,
> because it sounds like it would do the work for you. Thanks again.

Reply to this Message