T
tspraetz
I want to format cells to accept charge card numbers with the spaces.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
Kassie
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.
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.
T
tspraetz
Kassie,
I did that, but for some reason it changes the last digit to a zero.
I did that, but for some reason it changes the last digit to a zero.
D
DS NTE
Excel uses 15 digits, format as text.
knut
knut
tspraetz said:Kassie,
I did that, but for some reason it changes the last digit to a zero.
D
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.
Or you can prefix your entry with a single quote.
'1234123412341234
And it'll be treated as text.
T
tspraetz
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
digit. DS NTE was right about Excel usings 15 digits. Yes using "text" will
not help me in this situation.
Thanks
C
CLR
Yes using "text" will
Why not?
Vaya con Dios,
Chuck, CABGx3
not help me in this situation.
Why not?
Vaya con Dios,
Chuck, CABGx3
D
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
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
T
tspraetz
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.
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.
C
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
into it's own column, and then CONCATENATE them together at the end
with.......
=A1&" "&B1&" "&C1&" "&D1
Vaya con Dios,
Chuck, CABGx3
tspraetz said: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.