[JerryG]

I want to format 16 Digit CC#'s as 0000-0000-0000-0000. When I choose
Custom and enter it as I did above or using all # symbols
(####-####-####-####), it magically changes the last digit of the card # to a
zero. Any ideas what is causing this?

For Example:

9990090000092207 becomes 9990-0900-0009-2200

JerryG

[Dave Peterson]

Excel only keeps track of 15 significant digits for numbers. If you want more
than that, you have to enter your value as text (preformat the range as Text or
start each entry with an apostrophe '1234).

Saved from a previous post:

If you type the value as text (either pre-format the cell or text and then do
the data entry or start your entry with an apostrophe), then you can use this
technique:

Type this in A1:
'1234123412341234

and use this in that helper column:
=mid(a1,1,4)&"-"&mid(a1,5,4)&"-"&mid(a1,9,4)&"-"&mid(a1,13,4)

or you could use a worksheet event that does the work for you--but you still
need to enter the data as text!

If you want to try this idea, rightclick on the worksheet tab that should have
this behavior. Select view code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTempVal As Variant

On Error GoTo errhandler:

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub

myTempVal = CDec(Target.Value)
Application.EnableEvents = False
Target.Value = Format(myTempVal, "0000-0000-0000-0000")

errhandler:
Application.EnableEvents = True

End Sub


If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

JerryG wrote:

I want to format 16 Digit CC#'s as 0000-0000-0000-0000. When I choose
Custom and enter it as I did above or using all # symbols
(####-####-####-####), it magically changes the last digit of the card # to a
zero. Any ideas what is causing this?

For Example:

9990090000092207 becomes 9990-0900-0009-2200

JerryG

--

Dave Peterson

[Gord Dibben]

Excel recognizes only 15 significant figures.

No way to enter a 16 digit number without dropping the last digit and adding
a zero.

Unless you precede the 16 digits with an apostrophe.

But this becomes text and cannot be custom formatted as you wish.

In that case......after entering with apostrophe

Try a helper column with a formula.

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

When happy, select the helper column and Copy then paste
specialvaluesokesc

Delete the original column of text digits.


Gord Dibben MS Excel MVP

On Tue, 25 Aug 2009 15:47:07 -0700, JerryG
wrote:


I want to format 16 Digit CC#'s as 0000-0000-0000-0000. When I choose
Custom and enter it as I did above or using all # symbols
(####-####-####-####), it magically changes the last digit of the card # to a
zero. Any ideas what is causing this?

For Example:

9990090000092207 becomes 9990-0900-0009-2200

JerryG

[JerryG]

Thank you so much for answering this. It has been making me crazy. I did
not know about the 15 Digit thing. Several people I know have had this
problem and lost the credit card #'s and sometimes the revenue.
--
JerryG


"Gord Dibben" wrote:

Excel recognizes only 15 significant figures.

No way to enter a 16 digit number without dropping the last digit and adding
a zero.

Unless you precede the 16 digits with an apostrophe.

But this becomes text and cannot be custom formatted as you wish.

In that case......after entering with apostrophe

Try a helper column with a formula.

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

When happy, select the helper column and Copy then paste
specialvaluesokesc

Delete the original column of text digits.


Gord Dibben MS Excel MVP

On Tue, 25 Aug 2009 15:47:07 -0700, JerryG
wrote:


I want to format 16 Digit CC#'s as 0000-0000-0000-0000. When I choose
Custom and enter it as I did above or using all # symbols
(####-####-####-####), it magically changes the last digit of the card # to a
zero. Any ideas what is causing this?

For Example:

9990090000092207 becomes 9990-0900-0009-2200

JerryG

[JerryG]

Thank you so much for the reply. This has been making me crazy. I was not
aware of the 15 digit limit. It is the changing thelat digit to a Zero that
had me so confused.
--
JerryG


"Dave Peterson" wrote:

Excel only keeps track of 15 significant digits for numbers. If you want more
than that, you have to enter your value as text (preformat the range as Text or
start each entry with an apostrophe '1234).

Saved from a previous post:

If you type the value as text (either pre-format the cell or text and then do
the data entry or start your entry with an apostrophe), then you can use this
technique:

Type this in A1:
'1234123412341234

and use this in that helper column:
=mid(a1,1,4)&"-"&mid(a1,5,4)&"-"&mid(a1,9,4)&"-"&mid(a1,13,4)

or you could use a worksheet event that does the work for you--but you still
need to enter the data as text!

If you want to try this idea, rightclick on the worksheet tab that should have
this behavior. Select view code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTempVal As Variant

On Error GoTo errhandler:

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub

myTempVal = CDec(Target.Value)
Application.EnableEvents = False
Target.Value = Format(myTempVal, "0000-0000-0000-0000")

errhandler:
Application.EnableEvents = True

End Sub


If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

JerryG wrote:

I want to format 16 Digit CC#'s as 0000-0000-0000-0000. When I choose
Custom and enter it as I did above or using all # symbols
(####-####-####-####), it magically changes the last digit of the card # to a
zero. Any ideas what is causing this?

For Example:

9990090000092207 becomes 9990-0900-0009-2200

JerryG

--

Dave Peterson