?
Solved

Appending cells into one - Excel

Posted on 2011-10-19
11
Medium Priority
?
152 Views
Last Modified: 2012-05-12
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.
 
0
Comment
Question by:camtz
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36995622
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36995649
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36996000
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:NLITech
ID: 36996067
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
 

Author Comment

by:camtz
ID: 36996977
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
 
LVL 42

Expert Comment

by:dlmille
ID: 36996999
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
 
LVL 42

Expert Comment

by:dlmille
ID: 36997556
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
 
LVL 42

Expert Comment

by:dlmille
ID: 36997560
>>separatoosted)   <- what's that?

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

Cheers,

Dave
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 0 total points
ID: 36998205
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
 

Author Comment

by:camtz
ID: 37002999
Sorry, was not aware that you couldn't ask the same question twice.
0
 
LVL 50
ID: 37003192
Points changed to 0
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question