Insert value from different rows of one column into one cell separating by comma without space in excel 2003?

[Mohammad Rashed Iqbal]

I have the following data in column A, row 1 to row 261 in excel 2003:

0154066
0154067
0154068
0170793
0170794
0211150
0211157
0211158
0211159
0211160
0211161
0211162
0211163
0211164
0211165
0211166
0211167
0211168
0211169
0211170
0211171
0211172
0211173
0211174
0211175
0211176
0211177
0211178
0211179
0211180
0049256
0049257
0049258
0049259
0049260
0216829
0216831
0222099
0222100
0269172
0297009
0413981
0413982
0413983
0413984
0413985
0413986
0413987
0413988
0413989
0413990
0413991
0413992
0413993
0413994
0413995
0413996
0413997
0413998
0413999
0414000
0461906
0461908
0490743
0510643
0532819
0536083
0536084
0536604
0538418
0586937
0586938
0658694
0663176
0663177
0663185
0773442
0773443
0773445
0803006
0803007
0803008
0803009
0803010
0803011
0803012
0803013
0803014
0803015
0803016
0803017
0803018
0803019
0803020
0803021
0803022
0803023
0803024
0856997
0856998
0856999
0940101
0964501
0964502
0964503
0964504
0964551
0964552
0964553
0964554
0964555
0964556
0964557
0964558
0964559
0964560
0506273
0297269
0297270
0334718
0177876
0177893
0177899
0177900
0023731
0023732
0023733
0023734
0023735
0023736
0023737
0023738
0023739
0023740
0023741
0023742
0023743
0023744
0023745
0023746
0023747
0023748
0023749
0023750
0023711
0023712
0023713
0023714
0023715
0023716
0023717
0023718
0023719
0023720
0297069
0297070
0297071
0297072
0297073
0297074
0297075
0297076
0297077
0297078
0366344
0366345
0366346
0366347
0366348
0366349
0366350
0366351
0366352
0366353
0366354
0366355
0462636
0462637
0462638
0462639
0462640
0922891
0922892
0922893
0922894
0922895
0922896
0922897
0922898
0922899
0922900
0177887
0177888
0177889
0177890
0177891
0177892
0160555
0160556
0160557
0160558
0160559
0160560
0160561
0160562
0160563
0160564
0757401
0988090
0625061
0625062
0053303
0345168
0757154
0653005
0663101
0757110
0308002
0308094
0414970
0414972
0414973
0679965
0246677
0024781
0024537
0024538
0523601
0523607
0523609
0757183
0395465
0395466
0395467
0395468
0395469
0395470
0395471
0482912
0017784
0032309
0032310
0032311
0351862
0063809
0063810
0072582
0072583
0596489
0085002
0085003
0024014
0024015
0018697
0404660
0404661
0637781
0405978
0405979
0467305
0263162 now I want to insert all the data in B1 cell separating by comma without space in excel 2003.

<colgroup><col></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Re: How to insert value from different rows of one column into one cell separating by comma without space in excel 2003?

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub ConcatColumnA()
  Range("B1") = Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), ",")
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ConcatColumnA) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Mohammad Rashed Iqbal]

Re: How to insert value from different rows of one column into one cell separating by comma without space in excel 2003?

Thanks a lot. It works.

 

[Mike Szczesny]

Re: How to insert value from different rows of one column into one cell separating by comma without space in excel 2003?

Can you something like this? I created a very simple data sample. The formula is =concatenate(transpose(range&" ,")). Before you hit enter, you need to highlight the transpose(....) part of your formula with your mouse. Hit F9. Remove the { and }. Hit enter.

1		1 ,2 ,3 ,4 ,5 ,6 ,
2
3
4
5
6

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[Mohammad Rashed Iqbal]

Re: How to insert value from different rows of one column into one cell separating by comma without space in excel 2003?

Thanks a lot. It works.

Thanks. It is working.