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.


--
jbenet
------------------------------------------------------------------------
jbenet's Profile: http://www.excelforum.com/member.php...o&userid=29531
View this thread: http://www.excelforum.com/showthread...hreadid=492297

Hello,

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

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

HTH

Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=492297

Actually, I believe both CONCATENATE and ref&ref method work for all
number formats except TEXT. It sounds like your cells are formatted as
TEXT (FormatCellsNumber tabCategory: TEXT) which would return the
fomula and not it's returned value. Is that the case?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=492297

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


--
jbenet
------------------------------------------------------------------------
jbenet's Profile: http://www.excelforum.com/member.php...o&userid=29531
View this thread: http://www.excelforum.com/showthread...hreadid=492297

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.

"jbenet" wrote:


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.


--
jbenet
------------------------------------------------------------------------
jbenet's Profile: http://www.excelforum.com/member.php...o&userid=29531
View this thread: http://www.excelforum.com/showthread...hreadid=492297

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?


"jbenet" wrote in message
...

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


--
jbenet
------------------------------------------------------------------------
jbenet's Profile:
http://www.excelforum.com/member.php...o&userid=29531
View this thread: http://www.excelforum.com/showthread...hreadid=492297