Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refreshing formulas in cells | Excel Discussion (Misc queries) | |||
Copy range of cells omitting formulas that result in " " | Excel Discussion (Misc queries) | |||
loan calculator cells, P&I formulas do not copy to second cloumn | Excel Worksheet Functions | |||
Copy & Paste Visible Cells with Formulas | Excel Worksheet Functions | |||
how to copy formulas to other cells in excell | Setting up and Configuration of Excel |