Concantenate Help

[Terri]

I am trying to take several columns of data and condense each row into a one
sheet of information to put in a binder. I have tried the following formula,
based on Microsoft Office Assistance for combining two or more columns by
using a function. It didn't work.

Here is the formula I tried:

=CONCANTENATE(A1, " ", C1, " ", G1, " ", I1, " ", J1, " ", CHAR(10), L1, "
", CHAR(10), M1, " ", N1, " ", 01, CHAR(10), P1, CHAR(10), Q1, " ", R1,
CHAR(10), U1, CHAR(10), W1, CHAR(10), X1, CHAR(10), Y1)

This is my first time trying this, and I see by the posts that a lot of you
have fantastic experience, so please tell me what I am doing wrong.

Thanks,

Terri

 

[Duke Carey]

What do you mean "it doesn't work". You've asked several questions about
this formula. Perhaps if you gave us some sample data and an idea of what
you need to accomplish, we could solve this once and for all!

 

[Terri]

I am trying to take information from an existing worksheet and create a one
sheet for each row of the worksheet using some of the columns. I am trying
to combine columns, and when I paste the data to another worksheet, it comes
up as #REF!. I widened the column, but the data did not appear. Does that
help? And, yes I have asked several questions and didn't get any answers, so
that is why I am asking again.

 

[Duke Carey]

1) you've gotten lots of answers. Here's my explanation to you from 2 days ago

http://www.microsoft.com/office/com...3089&mid=729a9f62-31fc-4672-a414-d43d19d1a4d0

When you paste to another sheet, are you pasting the formula, or do you want
just the result? If the result, then when pasting use Edit>Paste
Special>Values



2) The explanation you've included below is WAAAY to vague to allow us to
really help you

3) Try concatenating just a couple of cells at a time. If that works, add
another cell or two to the formula. Continue until you get the error. that
will provide valuable insight that you can provide to us.

 

[Harlan Grove]

Terri wrote...

I am trying to take several columns of data and condense each row into a one
sheet of information to put in a binder. I have tried the following formula,
based on Microsoft Office Assistance for combining two or more columns by
using a function. It didn't work.

Here is the formula I tried:

=CONCANTENATE(A1, " ", C1, " ", G1, " ", I1, " ", J1, " ", CHAR(10), L1,
" ", CHAR(10), M1, " ", N1, " ", 01, CHAR(10), P1, CHAR(10), Q1, " ", R1,
CHAR(10), U1, CHAR(10), W1, CHAR(10), X1, CHAR(10), Y1)

....

Several errors, not least saying how it didn't work.

1. There's no function named CONCANTENATE. You want CONCATENATE.

2. You show 01 as one of the arguments. That's two numerals, a zero and
a one. I suspect you meant O1 - a capital letter o (the letter between
N and P) followed by the numeral one.

3. The critical problem is that you try to use more than 30 function
arguments. Excel is archaic in some places, and this is one of them.

You could use multiple nested CONCATENATE calls, e.g.,

=CONCATENATE(CONCATENATE(A1, " ", C1, " ", G1, " ", I1, " ", J1,
CHAR(10)),
CONCATENATE(L1, " ", CHAR(10), M1, " ", N1, " ", 01, CHAR(10), P1,
CHAR(10)),
CONCATENATE(Q1, " ", R1, CHAR(10), U1, CHAR(10), W1, CHAR(10)),
CONCATENATE(X1, CHAR(10), Y1))

but there's NEVER a good reason to use CONCATENATE rather than using
the concatenation operator, &. Change your formula to

=A1& " " & C1 & " " & G1 & " " & I1 & " " & J1 & CHAR(10) & L1 & " " &
CHAR(10)
& M1 & " " & N1 & " " & O1 & CHAR(10) & P1 & CHAR(10) & Q1 & " " & R1
& CHAR(10) & U1 & CHAR(10) & W1 & CHAR(10) & X1 & CHAR(10) & Y1