One of the things that Excel is missing is a way of joining a range of cell values with a common delimiter such as a comma. Here is an example of a range of cells that would be nice to be able to join together with a comma:

A B
1 This
2 is
3 a
4 strange
5 and
6 weird
7 sentence.

In order to join all of the values in the range A1 to A7 without a UDF (user-defined function) you can do the following:

  1. Enter =$A$1 into cell B1.
  2. Enter =CONCATENATE($B1," ",$A2) into cell B2.
  3. Re-select cell B2 and then use the CTRL + C key combination to copy it.
  4. Select cells B3 to B7 and then use CTRL + V key combination to paste the formula into those cells.
A B
1 This This
2 is This is
3 a This is a
4 strange This is a strange
5 and This is a strange and
6 weird This is a strange and weird
7 sentence. This is a strange and weird sentence.

Voilà! Now your desired concatenation of cells A1 to A5 can be seen in cell B5. It is important to note that this could have been done with much more than just five cells as well. If it bothers you having cells B1 to B4 filled with progressive listings of the values in column A, you can do the following:

  1. Select and copy cell B5.
  2. Select the cell into which you would like to paste the value.
  3. Go to the menu and select Edit and then Paste Special….
  4. In the dialog box, choose the paste Values option and then click OK.
  5. Clear the cells that you no longer want to be filled.
Categories: BlogMicrosoft Office

12 Comments

Roy Tinker · July 17, 2012 at 2:56 PM

Thanks! Recursive formula for the win.

Ted Takacs · August 23, 2012 at 2:53 PM

Thank you for your post. Very clever workaround for a significant gap in the functionality of Excel.

Ari · October 5, 2012 at 4:32 PM

This was really helpful to me, thanks so much for posting

Mike · October 11, 2012 at 7:00 AM

Nice one . Thanks

Laxman · November 8, 2012 at 3:43 PM

I had to concatenate more than 40 records.This saved my valuable time. Thanks !!

Mohd Shakeel · February 22, 2013 at 8:35 AM

This was really helpful for me. I saved more than 4 hours with the help of this.

I really appriciate.

John · May 15, 2013 at 5:45 AM

Genius in its simplicity!

Praveen · June 12, 2013 at 1:05 PM

Thank you so much! Its saved so much time.

Thanks again.

Gooral · July 25, 2013 at 6:22 AM

Thanks a lot!!!!

Noah · October 29, 2014 at 3:10 PM

Thank you! Really smart. Here is my twist, to leave out blank cells:


=CONCATENATE(B1,IF(ISBLANK(A2),""," "&A2))

Arthur · July 15, 2015 at 1:31 AM

Nice solution that avoids the use of macros/VBA !

Arthur · July 15, 2015 at 1:32 AM

Nice solution that avoids the use of macros and VBA!

Leave a Reply to John Cancel reply

Your email address will not be published. Required fields are marked *