Import Comma delimited info in a Column

[Guest]

I apolgize if this is the wrong Forum for this question- if so, please
redirect me. Thanks

I have hundreds of email addresses listed in a Wordpad file- entries are
separated by a comma. When I use the Excel Import function, all data is
successfully imported into an Excel spreadsheet- BUT in a single row. I would
like to have this info in a single column (A)- then I could easily
alphabetize the entries and eliminate duplicates.

Thanks for any help...

 

[davesexcel]

copy paste special, transpose
will paste your row into a column
or vice versa

 

[Ron Rosenfeld]

I apolgize if this is the wrong Forum for this question- if so, please
redirect me. Thanks

I have hundreds of email addresses listed in a Wordpad file- entries are
separated by a comma. When I use the Excel Import function, all data is
successfully imported into an Excel spreadsheet- BUT in a single row. I would
like to have this info in a single column (A)- then I could easily
alphabetize the entries and eliminate duplicates.

Thanks for any help...

The simplest method would be to use an editor which will allow you to replace
the commas with <CR> (carriage return). Word is one program that can do this.
Then import this converted file into Excel.
--ron

 

[Guest]

Thanks.. worked like a charm!

 

[Guest]

I had troubles with this method. I copied the names into Word and used the
find and replace function to replace the "," with a <CR>. Then I saved it and
attempted to open it as an Excel file. DID not work- file looked like machine
language. Then I copied the <CR> delimited to Notepad and attempted to open
this as an Excel file. This worked- but placed all entries in a single cell.
During the Import process Excel Text Import wizard asks about formatting-
tried to use <CR> as separator, but that did not work.

 

[Beege]

Burger,

One problem with text file editing in Word. If you save as a Word document,
(file.doc), it includes formatting and other information, so you can't use
it as a straight text file anymore. Any .CSV or .TXT file must be saved as a
..CSV or .TXT file, despite the warnings of formatting loss that Word may
give you. That is why most people would tell you to edit those files in
Notepad.

Beege

 

[Ron Rosenfeld]

I had troubles with this method. I copied the names into Word and used the
find and replace function to replace the "," with a <CR>. Then I saved it and
attempted to open it as an Excel file. DID not work- file looked like machine
language. Then I copied the <CR> delimited to Notepad and attempted to open
this as an Excel file. This worked- but placed all entries in a single cell.
During the Import process Excel Text Import wizard asks about formatting-
tried to use <CR> as separator, but that did not work.

Weird. Did you save it, from Word, as a TEXT document, or as a Word document.




--ron

 

[Guest]

I originally saved in Word as doc file. Then, after realizing my error, I
saved it as a txt file. But Excel does not understand how to open (import)
the txt in this format:

(e-mail address removed)<CR>[email protected]<CR>[email protected]<CR>

It wants to know what the delimiter is- and will not accept <CR> If no
delimiter is used, all email addresses are imported in a single cell.

Thanks for your help...

 

[Ron Rosenfeld]

I originally saved in Word as doc file. Then, after realizing my error, I
saved it as a txt file. But Excel does not understand how to open (import)
the txt in this format:

(e-mail address removed)<CR>[email protected]<CR>[email protected]<CR>

It wants to know what the delimiter is- and will not accept <CR> If no
delimiter is used, all email addresses are imported in a single cell.

Thanks for your help...

Did you actually type in the four characters <CR> in the replace field? If so,
that is the problem

I meant it as a euphemism for the Carriage Return ASCII code which, in Word, is
called a "Paragraph Mark". Since I didn't know which editor you might use, I
did not specify the particular routine for Word.

With your file loaded in Word

1. Edit/Replace
2. Find What: ,
Replace with:
More
Special
Select "Paragraph Mark"
(you should see ^p in the text box)
3. Then hit "Replace All"


(y
--ron

 

[Guest]

Thanks, Ron-- that worked. And I learned a bit about Word/Excel. And I did
save these instructions in a document that I will use next year when I have
to remember once again how to do this.

Onward...

 

[Ron Rosenfeld]

Thanks, Ron-- that worked. And I learned a bit about Word/Excel. And I did
save these instructions in a document that I will use next year when I have
to remember once again how to do this.

Whew. Glad we got that sorted out. Sometimes I use acronyms that "I" think
are commonly known (as I did with the <CR>) and that causes problems as it did
here.

Thanks for the feedback.

Best wishes,
--ron