Appending cells into one - Excel

Hi, I am trying to append, combine, consolidate or whatever word you want to use to put the content of cells B1 through Z1 into cell B1.  The data contains contact information including names numbers, addresses, etc.The list is very long but if I can figure out how to do it for one, I should be able to do the others as well.

I've tried different things but none have worked so for. - (Excel 2003)
Thanks.
 
camtzAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
camtz, this is a duplicate of the question that I've been spending the better part of two days trying to answer.

Dave, have a look at http:/Q_27399553.html

The concept of concatenation with & seems to elude the asker, so I'm not sure you'll get far with with a UDF.

I'm inclined to break out my Zone Advisor Powers and tag this question for deletion.

At the very least, the points for this duplicate and the original will need to be reduced to 250, so that the total points for the actual question do not top 500.

camtz, this is not how things are done at EE. You've been a member long enough to know about the guidelines.

I'm sorry I wasted my time in your other thread, very patiently trying (in vain) to explain the most basic concepts of the formula to you.

With regards to this:

barryhoudini:
As you can see from the screenshot, my system ADDS an extra " on to C.  I have tried it several times but it continues to do it.  Any idea why?

And as a general hint: Stop adding " signs at the end of formulas suggested here. People have been showing you perfectly working formulas. You take them and then add a " sign at the end and then they don't work. That's not the fault of the people who suggested the correct formula, it's the fault of the person who added the spurious " sign. Break out your reading skills and apply them.
0
 
dlmilleCommented:
The simplest way is to use a UDF function (though someone might come creative with a fancy formula  :)

tip:http://www.vbaexpress.com/kb/getarticle.php?kb_id=817

See attached, which uses the concRange() function on B1:Z1 (having just numbers) and a comma separator as the delimiter.


for this example, I just added this formula in A1:

[A1]=concRange(B1:Z1,",")

and the result was a comma delimited string of the values in B1:Z1

Dave
concatRange-r1.xls
0
 
barry houdiniCommented:
Without VBA or add-ins the only formula appoach would just be to concatenate all the cells like this in A1

=CONCATENATE(B1,", ",C1,", ",D1,", ",E1)

obviously extend that up to Z1

regards, barry
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
barry houdiniCommented:
0
 
NLITechCommented:
You could Save As the file as a Text (Tab delimited) file. Then open your new .txt file with Excel, the Text Import Wizard should come up and select fixed width.
0
 
camtzAuthor Commented:
dlmille:
Okay, please walk me through this.  Once I put this in A1, do I then go to the worksheet that contains the data and select the appropriate cells, copy them, and come back to the page with your example and paste it in cell B1?

barryhoudini:
As you can see from the screenshot, my system ADDS an extra " on to C.  I have tried it several times but it continues to do it.  Any idea why?
QKees-Screens.jpg
0
 
dlmilleCommented:
You can open the workbook I posted (saved to a trusted location - just ensure macros are enabled)

Then copy your B1:C1 and paste it in to "my" B1 and the result will show in A1

If your data has formulas just paste special values

That's all there is to it

Once you have it working I can take you through adding this function to any workbook so you'll have that documented for future.

Cheers

Dave
0
 
dlmilleCommented:
While I recommend the UDF function I posted for your use.  Here's a quick and dirty approach to keep in mind when you're "under the gun"...

If you think about it, its like building a cumulative sum, though you're doing it with text.  Here's an approach I used to take, some time back.

Go to cell Z2, and put this formula
[Z2]=Z1

Then, in Y2, put this formula - note comma separation - you can put whatever separatoosted) you need
[Y2]=Y1&","&Z2

Now, drag/copy Y2 all the way back to B2.  In B2, you'd have that concatenated list that you can copy/paste value as you need :)

See attached.

Dave
cumulative-r1.xls
0
 
dlmilleCommented:
>>separatoosted)   <- what's that?

I meant you can put whatever separator characters you might need, as with the UDF...

Cheers,

Dave
0
 
camtzAuthor Commented:
Sorry, was not aware that you couldn't ask the same question twice.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Points changed to 0
0
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.

All Courses

From novice to tech pro — start learning today.