Concatenating Cells

[jbenet]

I have spent hours this afternoon in Excel 2003 trying to concatenate
two adjacent text columns into a third column defined as Text format.
It doesn't work, the result cell just displays the formula you enter
{e.g. =A1&B1 or =CONCATENATE(A1,B1)}.

I discovered after a great deal of frustration that this will only work
if the cell containing the formula is formatted as '*General'*.

All the MS command help refers to the data being concatenated as 'text'
data as does the command help that displays as you type.

I found no help on this on the MS site and trawling the web came up
with nothing either.

It seems incredible to me that a standard text operation like this will
only work in a format of 'General' which is a pretty non-descript format
anyway - 'General format cells have no specific number format' is the
helpful description you get when applying this format to cells in
Excel.

...or maybe I've missed something. Any help gratefully received.
Many thanks.

 

[SteveG]

Hello,

The Text you have in the first two columns should not matter what th
format is. I think that the problem lies with the beginning format o
the cells that you are entering the formula in.

Start with formatting the cells where you are entering your formula a
general. Enter the formula. Drag down as needed. Then change th
formatting of those cells to Text if you want. It doesn't matter wha
formula you type in a cell, if it is formatted as Text, all you wil
get is the formula in Text. i.e. =A1+B1 will appear as is.

HTH

Cheers,

Stev

 

[Ron Coderre]

Actually, I believe both CONCATENATE and ref&ref method work for al
number formats except TEXT. It sounds like your cells are formatted a
TEXT (Format>Cells>Number tab>Category: TEXT) which would return th
fomula and not it's returned value. Is that the case?

Regards,
Ro

 

[jbenet]

Thanks you two guys. Yes, you are right but doesn't it seem odd that a
text manipulation function doesn't work with a result field
pre-formatted as text!?
John

 

[Sloth]

When you format a cell as text, you are not formatting the output; you are
telling excel to show exactly what is entered (which in your case looks like
a formula). Every other format is for manipulating numbers. And when text
is the result of a formula it ignores any formatting, and shows the result
exactly.

 

[JMay]

All spreadsheet formulas MUST begin with the "=" character (as originally
designed by MS), however they
expanded it to accomodate the Lotus 1-2-3 users by
granting/allowing the "+" or "-" signs to enter formulas.

Concatenation is a formula since it starts with the "="
character. NO MATTER what, if a cell is formatted
as Text the character "=" in the first position becomes
text, NOT the mathmatical - operator "=".

Does that help?