List comma separated

  • Thread starter Thread starter LinK
  • Start date Start date
L

LinK

I have an Excel mailing list and it has one email address on each line. The
Listmanager that I'm using required the addresses to be comma separated (no
spaces).

I tried basic 'help' and my >Save As >Other Formats >CSV (Comma Delimited)
(*csv) doesn't export comman separated!

I need the list to be like this:
myemailaddress.com,youremailaddress.com,hisemailaddrress.com,heremailaddress.com


Vista 64 bit - Excel 2007

Your help will be appreciated ...
 
One method.

Copy the column of addresses.

Open a new workbook.

Select A1 of sheet1 and Paste Special>Transpose.

Save the new file as *.csv

Second method.

Use this UDF to combine the addresses into one cell.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 2)
End Function

In B1 enter =ConCatRange(A1:A50)


Gord Dibben MS Excel MVP
 
This may work if the number of names/rows isn't too big.

Say your data is in A1:A999
Put this in B1:
=A1
Put this in B2:
=b1&","&a2
and drag down

B999 will have all the names separated by commas.

You could copy|paste that into NotePad and save from there -- or maybe import it
into whatever program you need.
 
Many thanks ... but I don't have a Past Special > Transpose choice
My choices are:
Microsoft Office Word Document Object
Picture (ENhanced Metafile)
HTML
Unicode Text
Text
 
I thought that column B would be empty. If column B is not empty, then insert a
new column B and type those formulas into B1 and B2.

They should be empty to start.

I'm not sure how you got sum or =Sum( into that cell.
I placed =A1 in B1 ... how do I get to B2, it says SUM?
Click to expand...
 
Thanks Dave. I have 672 email addresses, one on each line, some stretch all
the way into column D. So I think I understand a little better, to widen
column A so it contains the (width) of the longest email address. I'm good
with B1 ... but how do I get B2?


Dave Peterson said:
I thought that column B would be empty. If column B is not empty, then
insert a new column B and type those formulas into B1 and B2.

They should be empty to start.

I'm not sure how you got sum or =Sum( into that cell.
Click to expand...
 
I'm so sorry, I get it! B2 is row B line 2.

Many thanks!!!


Dave Peterson said:
I thought that column B would be empty. If column B is not empty, then
insert a new column B and type those formulas into B1 and B2.

They should be empty to start.

I'm not sure how you got sum or =Sum( into that cell.
Click to expand...
 
I don't understand the layout.

Are you saying that there are values in columns B, C or D?

Or are you saying that the values in column A extend to the right and cover
columns B, C, D?

If the email addresses are just wide but are all in column A, then try this:

Select column A (the entire column)
Then click on Format on the toolbar
Then Column
Then autofit selection

Then you should see that the values are entirely within column A and column B
looks empty.
Thanks Dave. I have 672 email addresses, one on each line, some stretch
all the way into column D. So I think I understand a little better, to
widen column A so it contains the (width) of the longest email address.
I'm good with B1 ... but how do I get B2?
Click to expand...
 
:( unfortunately, I'm back
When I place -A1 in B1 and go to B2, the first email address appears is B1
(as well as A1). When I place =b1&","&a2 in B2 it stays there ... but I
don't know what to drag. My pull down doesn't do anything.


LinK said:
I'm so sorry, I get it! B2 is row B line 2.

Many thanks!!!
Click to expand...
 
I don't understand what this means.

Does that mean you see the formula in the cell instead of a pair of email
addresses separated by a comma?

And take a look at Debra Dalgleish's site for using that autofill button. Maybe
it'll help.

http://contextures.com/xlDataEntry01.html#Mouse
:( unfortunately, I'm back
When I place -A1 in B1 and go to B2, the first email address appears is
B1 (as well as A1). When I place =b1&","&a2 in B2 it stays there ...
but I don't know what to drag. My pull down doesn't do anything.
Click to expand...
 
You are opening the new workbook in a separate instance of Excel.

Open the new workbook in the same instance and you will see the Paste
Special dialog.


Gord
 
This is a text only newsgroup.

But I'm sure you'll be forgiven by the Newsgroup gods for your minor infraction.

You didn't type the formula in B2 (yet).
 
Thank you so much for seeing this to conclusion. I have it now because of
your patience and direction.

Many thanks!
 
Thanks Gord


Gord Dibben said:
You are opening the new workbook in a separate instance of Excel.

Open the new workbook in the same instance and you will see the Paste
Special dialog.


Gord
Click to expand...
 
I'm back at it again only this time it's not working and I know it works.
What could I be doing wrong. I have the email addresses is column A and I
formatted so they auto fit column width. I placed =A1 in B1 ... and in B2 I
placed =b1&","&a2 ... at this point I see the comma separated format that
I'm after but I drag column B down and the addresses are not automatically
accumulating. I've tried everything. What could be wrong?

Thanks
 
Do you have calculation mode set to automatic?

Are you dragging B2 down?

Assume addresses in A1:A10

After dragging B2 down to B10 you should have 10 comma separated addresses
in B10


Gord Dibben MS Excel MVP

I'm back at it again only this time it's not working and I know it works.
What could I be doing wrong. I have the email addresses is column A and I
formatted so they auto fit column width. I placed =A1 in B1 ... and in B2 I
placed =b1&","&a2 ... at this point I see the comma separated format that
I'm after but I drag column B down and the addresses are not automatically
accumulating. I've tried everything. What could be wrong?

Thanks
Click to expand...
 
Thanks for replying ... Yes, I'm dragging B2 down but the addresses are not
accumulating.

I don't know if I'm set to automatic calculation mode. I Googled and found
this :

You can change these options using the Tools -->Options-->Calculation tab.

But now, (so sorry) I can't find "Tools"




Gord Dibben said:
Do you have calculation mode set to automatic?

Are you dragging B2 down?

Assume addresses in A1:A10

After dragging B2 down to B10 you should have 10 comma separated addresses
in B10


Gord Dibben MS Excel MVP

I'm back at it again only this time it's not working and I know it works.
What could I be doing wrong. I have the email addresses is column A and I
formatted so they auto fit column width. I placed =A1 in B1 ... and in B2
I
placed =b1&","&a2 ... at this point I see the comma separated format that
I'm after but I drag column B down and the addresses are not automatically
accumulating. I've tried everything. What could be wrong?

Thanks
Click to expand...
Click to expand...
 
Thanks for replying ... Yes, I'm dragging B2 down but the addresses are not
accumulating.

I don't know if I'm set to automatic calculation mode. I Googled and found
this :

You can change these options using the Tools -->Options-->Calculation tab.

But now, (so sorry) I can't find "Tools"



Gord Dibben said:
Do you have calculation mode set to automatic?

Are you dragging B2 down?

Assume addresses in A1:A10

After dragging B2 down to B10 you should have 10 comma separated addresses
in B10


Gord Dibben MS Excel MVP

I'm back at it again only this time it's not working and I know it works.
What could I be doing wrong. I have the email addresses is column A and I
formatted so they auto fit column width. I placed =A1 in B1 ... and in B2
I
placed =b1&","&a2 ... at this point I see the comma separated format that
I'm after but I drag column B down and the addresses are not automatically
accumulating. I've tried everything. What could be wrong?

Thanks
Click to expand...
Click to expand...
 
I found it under the Microsoft Office Button ... Excel Options ...
I'm set to Automatic
but it doesn't calculate.


Gord Dibben said:
Do you have calculation mode set to automatic?

Are you dragging B2 down?

Assume addresses in A1:A10

After dragging B2 down to B10 you should have 10 comma separated addresses
in B10


Gord Dibben MS Excel MVP

I'm back at it again only this time it's not working and I know it works.
What could I be doing wrong. I have the email addresses is column A and I
formatted so they auto fit column width. I placed =A1 in B1 ... and in B2
I
placed =b1&","&a2 ... at this point I see the comma separated format that
I'm after but I drag column B down and the addresses are not automatically
accumulating. I've tried everything. What could be wrong?

Thanks
Click to expand...
Click to expand...
 
Back
Top