Concatenate drops leading zeroes

  • Thread starter Thread starter PB
  • Start date Start date
P

PB

I am having a problem when concatenating 5 columns of 2
digit numbers which do have leading zeroes for 1 through 9.
Even though the column cells can be formated to view the
leading zeroes the formula drops them. I want it to keep
them.
IE.
03 47 59 07 35 should be 0347590735 which will be used in
sorting and grouping later in the process I am setting up.
Instead I get 34759735. As you can see this is a
completely different number.
 
Hy PB
try
=TEXT(A1,"00") & TEXT(B1,"00") & TEXT(C1,"00") & TEXT(D1,"00") &
TEXT(E1,"00")

The result would be text. If you need a number (with leading zeros) try
the following formula:
=VALUE(TEXT(A1,"00") & TEXT(B1,"00") & TEXT(C1,"00") & TEXT(D1,"00") &
TEXT(E1,"00"))
and format the cell with the custom format "00000000000"

HTH
Frank
 
Try

=TEXT(A1,"00")&TEXT(A2,"00")&TEXT(A3,"00")&TEXT(A4,"00")&TEXT(A5,"00")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top