Using formatted cells in formulas
 

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?

Frank,

Use something like

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

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Frank Marousek" wrote in message
...
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?

Excel is simply displaying the zeros, which are not really there (try
=Len(A1) to see). Using that principle, we can test the length of A1
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' digits
(00123)

HTH


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=377081

Two good suggestions... thanks!


--

"swatsp0p" wrote in
message ...

Excel is simply displaying the zeros, which are not really there (try
=Len(A1) to see). Using that principle, we can test the length of A1
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' digits
(00123)