Combined data from various cells

[sudipto3003]

Hi Friends,

I have a data sheet, where in col B there are numbers in multiple rows like B2=6000, B3=6001, B4=6009, etc., now I want to merge all those numbers in one cell say in C2 in the following way-
6000,6001,6009 and so on.
Need your help to solve this, thanks in advance.
I have attached a sample sheet too.

 

[brucef2112]

1. Select cell B2.
2. type =TRANSPOSE(B2:B4)&", " (do not hit [Enter] yet)
3. While still in the formula Press [F9] key. notice this displays the results with curly brackets and not the 'transpose() function.
   Example ={"6000, ","6001, ","6009, "}
   
   
4. Delete both curly brackets (first and last) in formula bar.
5. Directly after the last number there is an extra ", ". Delete this last comma and space, (be sure to leave the last double quote).
6. Go to the beginning of the formula just after the equal sign and type =Concatenate( in front of all characters in formula bar.
7. Type ) after last character in formula bar.
   Example: =CONCATENATE("6000, ","6001, ","6009")
8. Press [Enter]

Another way with the help of MS Word:
An easier way to do this is to copy the 100 rows in col A into Word. They will appear in Word as a table, with 100 rows. From the Word Table menu, choose Conver, then Table to Text. This will give you 100 rows separated by paragraph markers (invisible). Do a Find and Replace (CTRL+H) and replace ^p with a comma. ^p is the paragraph marker in Word, so once you do this you will get the values of the 100 rows separated by commas. You may have to delete extra commas at the end. Now copy and paste that from Word back into Excel.

 

[tomas]

Hi

you can use VBA function concat which I programmed . Go into cell write function concat as an argument select range of cells you want to concatenate.

 

[Khalid NGO]

Hi Sudipta,
You can also use =CONCATENATE(TRANSPOSE(your_range)&",")

as explain in detail here:
http://chandoo.org/wp/2014/01/13/combine-text-values-quick-tip/

Regards,

 

[sudipto3003]

Dear friend Tomas,
Thank you for your reply but sorry, as I am not an expert in VBA unable to follow your formula, will you please explain how to use it?

@Khalid NGO - Thank you for your suggestion which help me to solve the problem.

 

[sudipto3003]

@brucef2112
your second suggestion is very interesting, thank you friend.