How do I format charge card numbers?

[Guest]

I want to format cells to accept charge card numbers with the spaces.

 

[Guest]

Hi

with the selected cell, right click, select Format Cells, select custom, and
enter "0"'s where you want digits, and spaces where you want spaces, eg"0000
0000 0000 000". While when entering it will not have spaces, it will enter
correctly.

 

[Guest]

Kassie,

I did that, but for some reason it changes the last digit to a zero.

 

[DS NTE]

Excel uses 15 digits, format as text.

knut

Kassie,

I did that, but for some reason it changes the last digit to a zero.

 

[Dave Peterson]

You can format the cell as text (like DS NTE wrote).

Or you can prefix your entry with a single quote.

'1234123412341234

And it'll be treated as text.

 

[Guest]

I wanted to Custom Format the cells for charge card numbers that are 16
digit. DS NTE was right about Excel usings 15 digits. Yes using "text" will
not help me in this situation.

Thanks

 

[CLR]

Yes using "text" will

not help me in this situation.

Why not?

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

The bad news is that numberformat works with numbers. If you keep your data as
numbers, you lose the 16th character. If you change the value to text (either
preformatting or using the leading apostrophe), then numberformat won't work.

On way around it is to use a worksheet event that does the "formatting" for
you. It actually changes the value by inserting spaces.

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

But make sure whatever range you use is preformatted to text. If you leave it
general, then that 16th digit is already a 0 when the code starts.

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

 

[Guest]

I want to format this: 0000 0000 0000 0000 Charge Card Numer format. When you
type all the numbers down the column it would put the spaces in the number.
If you select text there is no place to enter your custom format like the
example above example.

 

[CLR]

OIC...........one way to "get there" would be to type each 4-digit group
into it's own column, and then CONCATENATE them together at the end
with.......

=A1&" "&B1&" "&C1&" "&D1

Vaya con Dios,
Chuck, CABGx3

I want to format this: 0000 0000 0000 0000 Charge Card Numer format. When you
type all the numbers down the column it would put the spaces in the number.
If you select text there is no place to enter your custom format like the
example above example.