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

M

Mohammad Rashed Iqbal

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

now I want to insert all the data in B1 cell separating by comma without space in excel 2003.[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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...
Code: 
[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.
 
Upvote 0
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.

[TABLE="width: 220"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]1 ,2 ,3 ,4 ,5 ,6 ,[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Extract list of cells from one column in to one cell and separate by a comma
Replies
8
Views
142
Peter_SSs
Peter_SSs
A
  • Question Question
VBA Split semicolon and comma separated values into new rows
Replies
9
Views
639
Adalinda
A
T
  • Solved
pivot table from columns with strings separated by commas
Replies
7
Views
1K
Fluff
Fluff
G
  • Solved
Pivot Table counting comma separated values filtered by Date
Replies
3
Views
421
Fluff
Fluff
R
  • Solved
Vlookup help for comma separated values
2
Replies
13
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top