Formatting Account Number

[Rich Knowles]

28-JUNE-2004 / Monday

I am trying to format an Excel 2002 cell to fill-in the dashes/hypens
of a 16 digit account number.

You'd think no big deal. I created the following custom format (w/o
quotes)
"0000-0000-0000-0000" based on the Social Security special format.

The problem is that when you enter all 16 digits MS-Excel (for some
unknown reason - at least to me) changes the last digit to zero.

I tried other format strings like # also to no avail.

Can anybody explain this to me, please?

Thanks.

--Rich K.

 

[Dave R.]

Because of precision limits on numbers! Try formatting them as text, then do
whatever formatting is necessary.

 

[RagDyer]

XL stops at 15 digit precision for numbers.
You must treat anything longer then that as "Text", which of coures, can't
take part in any calculations.
Since account numbers (credit cards) will not need to be calculated, you
should be able to deal with these "text" numbers.

On a cell by cell basis, you can prefix each entry with an apostrophe (').
On a larger scale, you can *pre-format* a column to "Text".

If you don't mind using text formulas, you can enter your numbers into a
pre-formatted text column, and have a "helper" column, which contains a text
formula, do the conversion to the format you're looking for.

For example, with your "numbers" starting in A1, enter this in B1 and copy
down as needed:

=LEFT(A1,4)&"-"&MID(A1,5,4)&"-"&MID(A1,9,4)&"-"&RIGHT(A1,4)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



28-JUNE-2004 / Monday

I am trying to format an Excel 2002 cell to fill-in the dashes/hypens
of a 16 digit account number.

You'd think no big deal. I created the following custom format (w/o
quotes)
"0000-0000-0000-0000" based on the Social Security special format.

The problem is that when you enter all 16 digits MS-Excel (for some
unknown reason - at least to me) changes the last digit to zero.

I tried other format strings like # also to no avail.

Can anybody explain this to me, please?

Thanks.

--Rich K.

 

[Dave R.]

Just to clarify - you can calculate based on numbers if they are formatted
in text. If you try to "convert" a 'text-number' by doing some math on it,
it will again cut it off as you have experienced if it is that long. You can
freely extract sections of the account numbers and do math on those, keeping
the precision limit in mind.

 

[Robin Hammond]

Rich,

The problem is with how excel stores numbers as doubles. If you enter
1234567890123456 in a normal worksheet cell, you are going to see
1.12347E+15, giving you a clue. Have a look at the value for the cell in the
formula bar and you will see what happened. It shows 1234567890123450. Excel
has truncated at 15 places.

I don't know how you are using this. If in a worksheet, the best solution I
can think of is to split the ac number into two cells and have a string
concatenation of the two values like this, otherwise you are simply going to
lose the last digit when you enter it a single the cell anyway.
=TEXT(C4,"0000-0000") &"-" & TEXT(D4,"0000-0000")

If in code, the trick is to use the Decimal datatype, although confusingly
this requires a type conversion and needs to be stored as a variant. Odd.
Sub Test()
Dim vTest As Variant
vTest = CDec(123456789012346#)
MsgBox Format(vTest, "0000-0000-0000-0000")
End Sub

HTH,

Robin Hammond
www.enhanceddatasystems.com