[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • 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!

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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