• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

Excel - Unable to add next cell to existing cell - Combining multple cells in one row (Concatenate?)

Hi Experts,

When I drag my formula from b2 to b3, excetera, I was thinking the output would be combining the contens of a2, a3, a4.  But it doesn't combine the next cell.  My example explains it better.

Any ideas on how to correct this?


Column A                                              B
1 johnsmith@yahoo.com      "johnsmith@yahoo.com",
2 johnroberts@gmail.com      "johnsmith@yahoo.com","johnroberts@gmail.com"
3 janesmith@hotmail.com      "johnsmith@yahoo.com","johnroberts@gmail.com","janesmith@hotmail.com"
4 janesmith@live.com      At this point this cell should = b3 + a4
5 jane@prodigy.net                     At this point this cell should = b4 + a5

My formulas:
a1 ='s email address a2 is blank
b2 ='s =""""&A2&""","
b3 ='s =""""&A2&""","&""""&A3&""""
b4 ='s =""""&A2&""","&""""&A3&""""&","&""""&A4&""""

  • 2
  • 2
1 Solution
Rory ArchibaldCommented:
A sample workbook would be helpful, but it appears you have answered your own question pretty much:
"At this point this cell should = b3 + a4"
and copy down.
byd2kAuthor Commented:
Thanks for the reply.  

The formula must be added to each line.  I am trying to have Excel automatically add the &""""&A5,6,7,etc"""" when it's copied from b5 through b2000....

I wonder if this needs to be a vb code versus a formula.

Rory ArchibaldCommented:
If you want the quotes added, it's just:
and copy down. See attached file.
byd2kAuthor Commented:
I found a site:   http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

I had to install a script and then it allowed me to use the following formua with Excel:


Thanks again for your assistance,
rorya's works for me!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now