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 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

[Ron Coderre]

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 (Format>Cells>Number tab>Category: TEXT) which would return the fomula and not it's returned value. Is that the case?

Regards,
Ron

[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.

"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
>
>

[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?


"jbenet" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>