Odd behavior using CONCATENATE

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

Hello,
I have one spreadsheet where I am trying to use CONCATENATE to combine
some text and a number.
The cell T6 contains "AT COST X "
The cell S7 contains a number 1.18
The cell S8 contains =CONCATENATE(T6,S7)

I get a #VALUE error on this, and I can't figure out why.

Any ideas?
 
Hello,
I have one spreadsheet where I am trying to use CONCATENATE to combine
some text and a number.
The cell T6 contains "AT COST X "
The cell S7 contains a number 1.18
The cell S8 contains =CONCATENATE(T6,S7)

I get a #VALUE error on this, and I can't figure out why.

Any ideas?
Click to expand...

I would like to add something to this...The worksheet I am working in
was originally developed in Excel 97. If I open a new workbook and
cut and paste this section into it, the code works fine.
 
First, I would have guessed that you use something like:
=CONCATENATE(T6,TEXT(S7,"0.00"))

Then that value in S7 would be nicely formatted in that concatenated string.

For your real question:
If you're using xl2003, then change this setting:
Tools|Options|Transition Tab|Uncheck "Transition formula evaluation"

In fact, if I were you, I'd uncheck all those options.

ps. If you had concatenated two text strings (like with the =text() version),
then you wouldn't have seen the error--well, not in this formula.
 
First, I would have guessed that you use something like:
=CONCATENATE(T6,TEXT(S7,"0.00"))

Then that value in S7 would be nicely formatted in that concatenated string.

For your real question:
If you're using xl2003, then change this setting:
Tools|Options|Transition Tab|Uncheck "Transition formula evaluation"

In fact, if I were you, I'd uncheck all those options.

ps.  If you had concatenated two text strings (like with the =text() version),
then you wouldn't have seen the error--well, not in this formula.
Click to expand...

I am using Excel 2007, but I unchecked the transition box anyway. I
tried the TEXT() function, and that one skips the numeric entry. So,
I am back to CONCATENATE. If I make a new sheet, cut and paste the
first 8 rows from sheet1 and paste to sheet2, the CONCATENATE function
works. So the problem is related to the sheet. What could be in the
sheet which is causing the function to misbehave?
 
What was the =text() formula that failed when you tried it?

What was in the cell that you used (try formatting as general and retyping it
first!).

Did you widen the column to see all the text?
 
Back
Top