=TEXT(A2,"000000")&TEXT(B2,"000000")&TEXT(C2,"0000 00")

when you reference a cell containing a number (formatted as a number) it
only looks at the value of the cell, not the displayed result. You have to
include the format within the TEXT function, like the formula above.

"RobynP" wrote:

I have three colums I am trying to concatenate they look like this:
A B C
Map Lot Unit
000001 000001 000000

I have had to create a custom format to make all of the zeros show. The
first 2 columns are formatted as 00000# and the last column is formatted as
00000#0.

When I enter the formula in a destination cell (Column D Row 2) I enter the
following formula: =CONCATENATE(A2,B2,C2)

As a result I get only the formula showing in the destination cell if
formatted as "Text", if the cell is formatted "General" I get a total of 110.

I would appreciate any help anyone can give.
Thanks!
Robyn