Number formatting question

[Robbie in Houston]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Robbie in Houston]

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

 

[Myrna Larson]

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).

 

[RagDyeR]

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!
==============================================

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

 

[easy]

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]

Thanks, that one does the trick.

 

[RagDyeR]

Thanks for the feed-back.