turning rows of text in excel into one continuous line of text

  • Thread starter Thread starter cam
  • Start date Start date
C

cam

I hope someone can help with this.
I have downloaded a long list of stock codes from the
standardandpoors.com website.
The website allows one to do this and formats the download
into one column of an excel spreadsheet with many rows.
The list of codes, some 100 to 200 of them in any
download, sit in unique cells e.g. Cell A2 is BHP, Cell A3
is CBA, Cell A4 is DBA etc. etc.
What I need to do is then transfer those codes into
Watchlists I set up in various free internet stock
tracking websites - CBS Marketwatch is a good example.
However, the Watchlists invariably ask one to enter the
codes horizontally usually separated by either a space or
a comma.
Now I've already worked out that I can transpose the
column of stock codes into one long row of stock codes.
The problem is that when I insert the rows from excel into
the Watchlist, only the first code appears because each
cell in the excel row is separated by columns.
What I need to do is to find a way to format that long row
of stock codes so that there's no column breaks between
stock codes and hopefully format that row so there's just
one space (or a comma) between each code.
Then I can bulk enter those codes into the Watchlist in
one go.
Any help would be much appreciated.
Kind regards,
Cargo
 
Cargo,

Try this. Assuming your codes are in cells A2:A100, then
in B2 enter:

=A2

and in cell B3 enter:

=B2&", "&A3

Now copy cell B3 to cells B4:B100 and in cell B100 you
should have your concatenated list of codes.

Hope this helps,
Ryan
 
Cargo,

First, you can transpose your data from rows to columns by
using the Copy / Paste Special / Transpose option.

Now that your data is in the right configuration, you can
save the file as a comma delimited format with the File,
Save As command. Under the drop list options is one for
CSV Comma Delimited *.csv. Once done, you can open the
file in Notepad to really see what it looks like.

HTH
Beth
Microsoft MVP - Excel
 
Ryan, thanks for doing that. Much appreciated.

-----Original Message-----
Cargo,

Try this. Assuming your codes are in cells A2:A100, then
in B2 enter:

=A2

and in cell B3 enter:

=B2&", "&A3

Now copy cell B3 to cells B4:B100 and in cell B100 you
should have your concatenated list of codes.

Hope this helps,
Ryan

.
Click to expand...
 
Back
Top