Convert Excel list

[LinK]

I have an Excel mailing list ... entries on on individual lines.

Is it possible to convert this list to the following format?
(e-mail address removed),[email protected],[email protected], on and on, comma
separated but with no spaces?

Many thanks,
LinK

 

[Bob I]

If less than 256, Copy ,Paste Special, Transpose, Save as .CSV.

 

[JBeaucaire]

Without an addin of any kind, try this.

1) All email address in column A
2) In B1, put the formula: =A1
3) In B2, put the formula:
=B1 & "," & A2
4) Copy the formula from B2 all the way down.

The last cell at the bottom will have the list you want.

5) Copy the last cell
6) Edit > Paste Special > Values

Now you can delete the values above and just use the value you've got in the
last cell.

Does that help?

 

[LinK]

My list has 800 but I took 250 and followed the directions but didn't get
any commas.

 

[LinK]

I follow you until #4 'copy the forumula all the way down' ... how do I do
this?
Thanks

 

[Bob I]

Did you open the CSV file in NotePad or other Text editor? If you
reopened it in Excel, Excel simply used the commas to parse the file
into separate cells.

 

[LinK]

Beautiful! I opened it in Excel initially. When I open in Notepad I see
the list as needed.
Many thanks!!!!
Linda

 

[JBeaucaire]

Many methods

1) Grab the lower right hand corner of the cell and drag downward
2) Double-click the lower right corner to auto-copy down
3) Highlight the cell, press CTRL-C to copy, highlight the rest of the
column and press ENTER

 

[Bob I]

You're welcome, have a great day!

Beautiful! I opened it in Excel initially. When I open in Notepad I
see the list as needed.
Many thanks!!!!
Linda

 

[LinK]

I'm finally working on the list. My test went well but now, I copy, paste
special, but don't see "transpose"
What am I missing?

Thanks!

 

[LinK]

Wonder what you mean by #4? How do I copy the formula from B2 all the way
down?

 

[Bob I]

Lower center of the Paste Special options panel, to the right of "Skip
blanks"

 

[LinK]

Thanks Bob but it was a different set of choices. The only way I could get
the document to produce 'transpose' was to make new rows about the list in
column A and paste back into the 'same' document. Pasting into a new
document produced (http://www.kwgroup.com/Image2.gif). I 'think' it has
something to do with how the
page saved ... the_train2.xls SHeet 1!R1C1:R200C7 ... did some formula tag
along?
Thanks Bob!

 

[Bob I]

Did you perhaps copy the original from an html page or document?

 

[LinK]

No, but the Excel file has columns a-z and then additional AA, AB, AC, etc.
it contains a lot of information. Email addresses are in column I ... which
I copied and pasted into a new document ... and tried to go from there.

 

[Gord Dibben]

Are you copying from one workbook to another?

If so you may be running two instances of Excel with a workbook open in
each.

Close one instance and open both workbooks in a single instance.


Gord Dibben MS Excel MVP

 

[LinK]

You really know your stuff!

I closed the original and when I Paste Special ... I still get the same
window but with fewer choices ... not the 'transpose' window.
I notice at the top of the document it says 'compatibility mode'

 

[Gord Dibben]

Do you have both workbooks open in the same instance?

Have you re-tried the copy then paste special?

No good just "closing the original" and trying to paste the existing
clipboard contents.


Gord

 

[LinK]

I copied from the original, close that instance and pasted into a new
(blank) excel doc ... at that point I get fewer choices but not the right
options (transpose)

 

[Gord Dibben]

Do it as I instructed.

Do not copy from original then close and paste.

Close first then open both workbooks in one Excel instance.

Now do your copy/paste special.


Gord