keeping format

[Maurice Samueks]

I have an excel spreadsheet with 3 digit numbers and some lead with
zeros. I went into format cell and created my own format so that I get
the zeros no matter how many there is. My format is just a simple
simple custom 000. Now after I get these numbers in this format I need
to cancotanate(&) them to create a 6 digit number. The problem is when
I cancontanate them together I loose the zeros that were added because
of formating. How can I get those zeros to remain during the
cancontanate process? example 916-069 then 916 | 69 then 91669 the
result I am looking for is 916-069 = 916069.

 

[JulieD]

Hi Maurice

with 916 in A1 and 069 in A2

=text(A1,"000") & text(A2,"000")

Cheers
JulieD

 

[Maurice Samueks]

that is great, just curious is it possible to replace that text with
numbers or is this particular formatting a text only option.

 

[JulieD]

Hi Maurice

using
=value(text(A1,"000") & text(A2,"000"))

will convert the answer back to a number
the problem with this is if the value in A1 is 2 digits long you'll only get
the 2 digits as numbers drop the leading zeros, so you'll have to put a
custom format on the cell ....

Cheers
JulieD