I need some help with Excel. I have a worksheet with over 1,000 rows. I
want to copy one column, which contains a value format like €œ1234-567€�, and
transpose the values to a row. I cant use the traditional €˜copy-and-paste
special €“ transpose because there are too many cells across. Once I have a
row, I need to make it into one long string where each value is separated by
commas. Does anyone know how to do this?

I tried to transpose my column in chunks, into rows, and then copying into
Word, but Word brings over the borders so it displays as a table €“ which I
dont want. And I dont know how to add the comma separators.


Thanks for any help you can provide.

Hi Villy

Try the below macro which will create a .csv file from the column
data..Adjust the range A1:A100 to suit your requirement. If you are new to
macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()


Sub Macro()
Dim intFile As Integer
Dim rngTemp As Range
Set rngTemp = Range("A1:A100")

intFile = FreeFile
Open "c:\temp.csv" For Output As #intFile
Print #intFile, Join(WorksheetFunction.Transpose(rngTemp), ",")
Close #intFile

End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Villy" wrote:

I need some help with Excel. I have a worksheet with over 1,000 rows. I
want to copy one column, which contains a value format like €œ1234-567€�, and
transpose the values to a row. I cant use the traditional €˜copy-and-paste
special €“ transpose because there are too many cells across. Once I have a
row, I need to make it into one long string where each value is separated by
commas. Does anyone know how to do this?

I tried to transpose my column in chunks, into rows, and then copying into
Word, but Word brings over the borders so it displays as a table €“ which I
dont want. And I dont know how to add the comma separators.


Thanks for any help you can provide.

Leave your data in that column (assume column A, starting with A1).
Put this formula in B1:

=TEXT(A1,"0000-000")

Then in B2 you can put this formula:

=B1&", "&TEXT(A2,"0000-000")

and then copy this down to the bottom of the data in column A. A quick
way of doing this is to select B2, and then double-click on the fill
handle (the small black square in the bottom right hand corner of the
cursor). The combined data that you want will be in the bottom cell of
column B. Select that cell, click <copy, then right-click and choose
Paste Special | Values (check) | OK then <Esc. Then you could move
that cell to another more-convenient location at the top of your
sheet.

You can get rid of all the other formulae in column B above that cell.

Hope this helps.

Pete

On Sep 8, 6:54*pm, Villy wrote:
I need some help with Excel. *I have a worksheet with over 1,000 rows. *I
want to copy one column, which contains a value format like “1234-567”, and
transpose the values to a row. *I can’t use the traditional ‘copy-and-paste
special – transpose’ because there are too many cells across. *Once I have a
row, I need to make it into one long string where each value is separated by
commas. *Does anyone know how to do this?

I tried to transpose my column in chunks, into rows, and then copying into
Word, but Word brings over the borders so it displays as a table – which I
don’t want. *And I don’t know how to add the comma separators.

Thanks for any help you can provide.

Sorry, I assumed you wanted a comma and space between the values. If
you only want a comma, then the formula in B2 should be:

=B1&","&TEXT(A2,"0000-000")

Hope this helps.

Pete

On Sep 8, 7:09*pm, Pete_UK wrote:
Leave your data in that column (assume column A, starting with A1).
Put this formula in B1:

=TEXT(A1,"0000-000")

Then in B2 you can put this formula:

=B1&", "&TEXT(A2,"0000-000")

and then copy this down to the bottom of the data in column A. A quick
way of doing this is to select B2, and then double-click on the fill
handle (the small black square in the bottom right hand corner of the
cursor). The combined data that you want will be in the bottom cell of
column B. Select that cell, click <copy, then right-click and choose
Paste Special | Values (check) | OK then <Esc. Then you could move
that cell to another more-convenient location at the top of your
sheet.

You can get rid of all the other formulae in column B above that cell.

Hope this helps.

Pete

On Sep 8, 6:54*pm, Villy wrote:



I need some help with Excel. *I have a worksheet with over 1,000 rows.. *I
want to copy one column, which contains a value format like “1234-567”, and
transpose the values to a row. *I can’t use the traditional ‘copy-and-paste
special – transpose’ because there are too many cells across. *Once I have a
row, I need to make it into one long string where each value is separated by
commas. *Does anyone know how to do this?

I tried to transpose my column in chunks, into rows, and then copying into
Word, but Word brings over the borders so it displays as a table – which I
don’t want. *And I don’t know how to add the comma separators.

Thanks for any help you can provide.- Hide quoted text -

- Show quoted text -

Copy this UDF to a general module in your workbook.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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) - 1)
End Function

Usage is: =concatrange(A1:A1000)

Then copy/paste specialValues


Gord Dibben MS Excel MVP

On Tue, 8 Sep 2009 10:54:08 -0700, Villy
wrote:

I need some help with Excel. I have a worksheet with over 1,000 rows. I
want to copy one column, which contains a value format like “1234-567”, and
transpose the values to a row. I can’t use the traditional ‘copy-and-paste
special – transpose’ because there are too many cells across. Once I have a
row, I need to make it into one long string where each value is separated by
commas. Does anyone know how to do this?

I tried to transpose my column in chunks, into rows, and then copying into
Word, but Word brings over the borders so it displays as a table – which I
don’t want. And I don’t know how to add the comma separators.


Thanks for any help you can provide.