Use numbers in CONCATENATE formula without getting error
 

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

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.



"JSS" wrote in message
...
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

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"JSS" wrote in message
...
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

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.

"Dave R." wrote:

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.



"JSS" wrote in message
...
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

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

"Ken Wright" wrote:

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"JSS" wrote in message
...
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

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

"JSS" wrote in message
...
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.