Custom Formats

[becky]

I have created a custom format for a cell that will be used in a general
company form. The custom format is for a credit card number and was written
as follows: #### #### #### ####. When ever I type in a number 9999 9999
9999 9999 the final digit is convert to a zero. I do not understand why it
is doing this. It is not being rounded. Can anyone help correct this very
irritating formatting issue?

Thanks

 

[Peo Sjoblom]

Excel has only 15 digits precision and you can't use a custom text format
that way,
You need to either preformat as text or precede the entry with an apostrophe


Regards,

Peo Sjoblom

 

[Frank Kabel]

Hi
Excel only supports 15 significant digits. So you can't have a number format
with 6 digits. You can enter a credit card number only if:
- you preformat the cell as text
- or enter the data with a preceding apostrophe '

in both cases you can't use a custom format though

 

[Dave Peterson]

Nope.

excel stores 15 significant digits.

You can preformat the cell as text or start with a leading apostrophe to get all
the numbers to show.

But then you'll have to format it manually.

Actually, you could have a worksheet event looking to see if that cell needs to
be reformatted.

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

I used all of column A in this line:
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
but you could use:
If Intersect(Target, Me.Range("c7:g99")) Is Nothing Then Exit Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[becky]

I am sorry. I still do not understand. I opened the document and keyed in
an apostrophe and the a 16 digit number. But my numbers all run together.
(there are no spaces in between) How would I preformat text?

bb

 

[Peo Sjoblom]

You can't custom format text, since you must use text you either have to use
a help formula or a a macro that will put in those spaces

formula example would be

=MID(A1,1,4)&" "&MID(A1,5,4)&" "&MID(A1,9,4)&" "&MID(A1,13,4)


Regards,

Peo Sjoblom

 

[Mark]

Hi,
why don't you use four separate cells for each of the four
digit numbers.

- Mark