Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Number formatting question
I'm trying to format a column of numbers for USPS tracking numbers.
However, no matter how many different ways I try the custom number format option, it only picks up the first 15 digits of the 22 digit number, and the rest display as zeros. It changes my input from a 22 digit number to a 15 digit number with E+21 on the end of it. If it's easier to look at, try this. The tracking number SHOULD display as: 9101 1288 8230 0160 7398 49 but it ACTUALLY displays: 9101 1288 8230 0160 0000 00 and the cell INPUT BECOMES: 9.10112888230016E+21 It seems to me the easy custom format would be: #### #### #### #### #### ## This doesn't work and I've tried about twenty different variations on that format. I know this is either simple or can't be done but my brain is just shot. Any help would be awesome. Thanks, Robbie |
#2
|
|||
|
|||
On Tue, 15 Mar 2005 01:14:08 -0600, "Robbie in Houston"
wrote: I'm trying to format a column of numbers for USPS tracking numbers. However, no matter how many different ways I try the custom number format option, it only picks up the first 15 digits of the 22 digit number, and the rest display as zeros. Excel's specifications: Number precision: 15 digits So any NUMBER you enter over 15 digits will only display the first 15 significant digits. In order to enter longer strings of numeric data, you must format the cell as TEXT. --ron |
#3
|
|||
|
|||
On Tue, 15 Mar 2005 01:14:08 -0600, "Robbie in Houston"
wrote: I'm trying to format a column of numbers for USPS tracking numbers. However, no matter how many different ways I try the custom number format option, it only picks up the first 15 digits of the 22 digit number, and the rest display as zeros. Hit <enter too soon. In order to enter the longer numbers, you must pre-format the cells as text or precede the entry with an apostrophe ('). The latter tells Excel to treat the string as TEXT, but will not display in the cell. --ron |
#4
|
|||
|
|||
Thanks Ron, that's what I was afraid of. I tried to use the text format
option but I would still like it to display the spaces properly without having to type <space between each group of numbers. Is this possible, or does the text format have the same limitation? Thanks, Robbie |
#5
|
|||
|
|||
You may be able to get it to work, but in general a format like, e.g.
00 000 00000 00 will only work when the data is a number. It won't work for text. But I've had some luck with, e.g. 9999999999999999 (16 digits) working with the format 0000 0000 0000 0000 It shouldn't, but it did (XL XP). On Tue, 15 Mar 2005 07:24:59 -0600, "Robbie in Houston" wrote: Thanks Ron, that's what I was afraid of. I tried to use the text format option but I would still like it to display the spaces properly without having to type <space between each group of numbers. Is this possible, or does the text format have the same limitation? Thanks, Robbie |
#6
|
|||
|
|||
You could try using 2 columns.
First, you must format your input column as Text, just so XL will permit the entry of your 22 digits without changing any of them. This input column could be placed in any "out-of-the-way" area of the sheet. Then, use a Text formula in the presentation column, to parse the input column data to the display appearance that you're looking for. Input data - J2:j20 Enter this formula in say Column A: =LEFT(J2,4)&" "&MID(J2,5,4)&" "&MID(J2,9,4)&" "&MID(J2,13,4)&" "&MID(J2,17,4)&" "&RIGHT(J2,2) This works for exactly 22 digits. If you might need more, just drop the Right function and add more MID()s. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Robbie in Houston" wrote in message ... Thanks Ron, that's what I was afraid of. I tried to use the text format option but I would still like it to display the spaces properly without having to type <space between each group of numbers. Is this possible, or does the text format have the same limitation? Thanks, Robbie |
#7
|
|||
|
|||
Hi,
will u use the numbers for reporting? actually, I guess you get the numbers from a database while puttin' the data on excel I've tried a "tracking number.txt" file which contains 14 rows of numbers like u exampled below 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 9101 1288 8230 0160 0000 00 while opening the data set use "Text Import Wizard" and make these adjustments; select-- fixed with, and point necessary arrows (for every space in the data set) select-- next select-- for every seperate column "Column data format as TEXT" after importing the data u can use "concatenate" function with " " (space) to gather the columns in the necessary format ex. =concatenate(A1;" ";B1;" ";C1;" ";D1;" ";E1;" ";F1) I wish this answer will be useful for u take care "Robbie in Houston" wrote in message ... I'm trying to format a column of numbers for USPS tracking numbers. However, no matter how many different ways I try the custom number format option, it only picks up the first 15 digits of the 22 digit number, and the rest display as zeros. It changes my input from a 22 digit number to a 15 digit number with E+21 on the end of it. If it's easier to look at, try this. The tracking number SHOULD display as: 9101 1288 8230 0160 7398 49 but it ACTUALLY displays: 9101 1288 8230 0160 0000 00 and the cell INPUT BECOMES: 9.10112888230016E+21 It seems to me the easy custom format would be: #### #### #### #### #### ## This doesn't work and I've tried about twenty different variations on that format. I know this is either simple or can't be done but my brain is just shot. Any help would be awesome. Thanks, Robbie |
#8
|
|||
|
|||
Thanks, that one does the trick.
"RagDyeR" wrote in message ... You could try using 2 columns. First, you must format your input column as Text, just so XL will permit the entry of your 22 digits without changing any of them. This input column could be placed in any "out-of-the-way" area of the sheet. Then, use a Text formula in the presentation column, to parse the input column data to the display appearance that you're looking for. Input data - J2:j20 Enter this formula in say Column A: =LEFT(J2,4)&" "&MID(J2,5,4)&" "&MID(J2,9,4)&" "&MID(J2,13,4)&" "&MID(J2,17,4)&" "&RIGHT(J2,2) This works for exactly 22 digits. If you might need more, just drop the Right function and add more MID()s. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Robbie in Houston" wrote in message ... Thanks Ron, that's what I was afraid of. I tried to use the text format option but I would still like it to display the spaces properly without having to type <space between each group of numbers. Is this possible, or does the text format have the same limitation? Thanks, Robbie |
#9
|
|||
|
|||
Thanks for the feed-back.
-- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "Robbie in Houston" wrote in message ... Thanks, that one does the trick. "RagDyeR" wrote in message ... You could try using 2 columns. First, you must format your input column as Text, just so XL will permit the entry of your 22 digits without changing any of them. This input column could be placed in any "out-of-the-way" area of the sheet. Then, use a Text formula in the presentation column, to parse the input column data to the display appearance that you're looking for. Input data - J2:j20 Enter this formula in say Column A: =LEFT(J2,4)&" "&MID(J2,5,4)&" "&MID(J2,9,4)&" "&MID(J2,13,4)&" "&MID(J2,17,4)&" "&RIGHT(J2,2) This works for exactly 22 digits. If you might need more, just drop the Right function and add more MID()s. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Robbie in Houston" wrote in message ... Thanks Ron, that's what I was afraid of. I tried to use the text format option but I would still like it to display the spaces properly without having to type <space between each group of numbers. Is this possible, or does the text format have the same limitation? Thanks, Robbie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell formatting behaviour question | Excel Discussion (Misc queries) | |||
Number formatting | Excel Discussion (Misc queries) | |||
Conditional formatting row when cell value is an even number | Excel Worksheet Functions | |||
Pivot Table Drill Down Number Formatting | Excel Discussion (Misc queries) | |||
Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions |