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?

On Mar 10, 6:18*pm, Andrew wrote:
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?

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.

Andrew wrote:

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?

--

Dave Peterson

On Mar 10, 7:11*pm, Dave Peterson wrote:
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.

Andrew wrote:

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?

--

Dave Peterson

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?

Andrew wrote:

On Mar 10, 7:11 pm, Dave Peterson wrote:
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.

Andrew wrote:

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?

--

Dave Peterson

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?

--

Dave Peterson