Solved

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

Posted on 2011-10-05
310 Views
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?

Thanks,
Byd2k

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&""""

0
Question by:byd2k

LVL 85

Accepted Solution

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"
=B3&","&A4
and copy down.
0

Author Comment

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.

-byd2k
0

LVL 85

Expert Comment

If you want the quotes added, it's just:
=B3&","""&A4&""""
and copy down. See attached file.
emails.xls
0

Author Comment

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

=concat(a2:a1000,"','")

byd2k
0

LVL 4

Expert Comment

rorya's works for me!
0

## Featured Post

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…