Using formatted cells in formulas

[Frank Marousek]

I have been provided a worksheet with a list of 5-digit part numbers that
the creator of the worksheet formatted as zip code to preserve leading
zeros. I need to use these cells in a formula which uses the "&" operator to
tack on a suffix. For example:

Original part number cell A1 = 2345 (with zip code formatting displays as
02345)

I need cell A2 to be 02345XX, so I'm using the following formula: A1&"XX".

Problem is, when I do this, Excel drops the leading zero and displays
2345XX. Is there a way to have Excel use the formatted display string for
cell A1 instead of the underlying numeric value?

 

[Earl Kiosterud]

Frank,

Use something like

=TEXT(A1, "00000") & "XX"

 

[swatsp0p]

Excel is simply displaying the zeros, which are not really there (tr
=Len(A1) to see). Using that principle, we can test the length of A
and add zeros as needed:

in cell A2:
=IF(LEN(A1)=4,"0"&A1&"XX",IF(LEN(A1)=3,"00"&A1&"XX",A1&"XX"))

I am assuming no part code number is fewer than three 'true' digit
(00123)

HT

 

[Frank Marousek]

Two good suggestions... thanks!