Convert Excel list

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

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),you@yourdomain.com,them@theirdomain.com, on and on, comma
separated but with no spaces?

Many thanks,
LinK
 
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?
 
I follow you until #4 'copy the forumula all the way down' ... how do I do
this?
Thanks
 
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.
 
Beautiful! I opened it in Excel initially. When I open in Notepad I see
the list as needed.
Many thanks!!!!
Linda
 
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
 
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!
 
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!
 
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.
 
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
 
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'
 
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
 
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)
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

.csv to comma separated 4
List comma separated 21
change csv list separator in excel 2007 Dutch version 1
Forumula to Extract Data 15
Rules and Reply-to E-Mail addresses 1
Printing SQL query results in a list of columns, instead of one long column 1
Stopping excel from converting numbers into dates on import 10
Converting .txt file to Excel 1

Back
Top