end number keeps changing to '0'
 

Hello,

I am trying to keep trackof credit card numbers, i put in a custom formating
that looks like this: ####-####-####-#### so i can just type the numbers in
without having to type '-'.

The only problem is that is always turns the last number into a '0' zero.

for instance if the number is:
4519-3589-2000-1875

the formula changes the number to this:
4519-3589-2000-1870

Thank you,

shivaraj

end number keeps changing to '0'
 

Excel only handles 15 digits so for Credit Cards (and long telephone
numbers!) you should format your cells as TEXT.

"Shivaraj" wrote:

Hello,

I am trying to keep trackof credit card numbers, i put in a custom formating
that looks like this: ####-####-####-#### so i can just type the numbers in
without having to type '-'.

The only problem is that is always turns the last number into a '0' zero.

for instance if the number is:
4519-3589-2000-1875

the formula changes the number to this:
4519-3589-2000-1870

Thank you,

shivaraj

end number keeps changing to '0'
 

When I format it as text it gives me, 5.4575E+15
I can put a ['] in fron of the numbers and it will work. but is there a way
for me to just type in the numbers and have it format like this:
4525-4545-4585-5656

thank you

"Toppers" wrote:

Excel only handles 15 digits so for Credit Cards (and long telephone
numbers!) you should format your cells as TEXT.

"Shivaraj" wrote:

Hello,

I am trying to keep trackof credit card numbers, i put in a custom formating
that looks like this: ####-####-####-#### so i can just type the numbers in
without having to type '-'.

The only problem is that is always turns the last number into a '0' zero.

for instance if the number is:
4519-3589-2000-1875

the formula changes the number to this:
4519-3589-2000-1870

Thank you,

shivaraj

end number keeps changing to '0'
 

When I format it as text it gives me, 5.4575E+15
I can put a ['] in fron of the numbers and it will work. but is there a way
for me to just type in the numbers and have it format like this:
4525-4545-4585-5656

"Toppers" wrote:

Excel only handles 15 digits so for Credit Cards (and long telephone
numbers!) you should format your cells as TEXT.

"Shivaraj" wrote:

Hello,

I am trying to keep trackof credit card numbers, i put in a custom formating
that looks like this: ####-####-####-#### so i can just type the numbers in
without having to type '-'.

The only problem is that is always turns the last number into a '0' zero.

for instance if the number is:
4519-3589-2000-1875

the formula changes the number to this:
4519-3589-2000-1870

Thank you,

shivaraj

end number keeps changing to '0'
 

The only solution I havefound is to:

Format cells as text and then enter the data (ay in Column A)

In column B put =TEXT(A1,"0000-0000-0000-0000") and copy down

At some point. copy/paste special=values

OR

Place this code in worksheet (right click on tab, view code and copy and
paste)

Change Range("A:A") to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo wsexit:

Set isect = Application.Intersect(Range("A:A"), Target)
If isect Is Nothing Then
' MsgBox "Ranges do not intersect"
Else
Target.Value = Format(Target.Value, "0000-0000-0000-0000")
End If
wsexit:
Application.EnableEvents = True
End Sub




"Shivaraj" wrote:

Hello,

I am trying to keep trackof credit card numbers, i put in a custom formating
that looks like this: ####-####-####-#### so i can just type the numbers in
without having to type '-'.

The only problem is that is always turns the last number into a '0' zero.

for instance if the number is:
4519-3589-2000-1875

the formula changes the number to this:
4519-3589-2000-1870

Thank you,

shivaraj