Use numbers in CONCATENATE formula without getting error

[JSS]

I am trying to use the CONCATENATE function in Excel 2002. In the help
section is shows using a cell with a number only. When I include a number
cell I get a #value error. How can I use a number with this function?
Help!! Thanks

 

[Dave R.]

What formula are you using now (include the content of the cells being
referenced)?

Unless you're trying to do a math operation on text, or referencing a cell
that has a #VALUE! errorI don't know how you would get a #VALUE! error.

 

[Ken Wright]

A1= abc
A2=123
A3= =A1&A2 = abc123

 

[JSS]

The formula being used is this: "=CONCATENATE(B10,A10)" B10=5 and A10=DATA.
The number was created by dragging the cursor down the row making it a
general format. I even changed the formating for the cell to text. It
results in the #value being returned in the cell.

However, when I type in the number directly making it store the number as
text instead of a number, the function works creating "5DATA". (Excel will
give the error dropdown box and say the number is stored as text. If you
store it as number it will then create the #value error.)

I'm not sure if this is a glitch in the system or not. I can't repeat the
error on a new spreadsheet. Perhaps there is some formating or hidden issue
with the spreadsheet I was using.

Thanks for the help.

 

[Numfric]

CONCATENATE expects TEXT operands. If a cell contains a number (rather than
a numeric value stored as text) you must use the TEXT(number,format) function
to convert it to text. e.g. TEXT(A2,"0")

 

[Maglor]

That's not correct. CONCATENATE works perfectly with numbers (typed in
or with a reference to a cell containing a number). There is no need
to convert them to text.

As for JSS' problem, I don't have a clue... :-(

 

[Dave R.]

I'm not sure then. Have you tried what Ken suggested (=B10&A10)?

Its worth a shot, but I don't know if it will help in your case. When I try
it, CONCATENATE or the A1&B1 method works fine independent of the cell
content or format (as long as it's not an error message).