Cell formatting for 16 digit numbers

[mgauci]

Somebody please help !
I need to find a way to format a cell so that when I type in a 16 digit credit card number the formatting will appear as the 16 digit number with a dash between every fourth digit. I can set this up by using ####-####-####-#### and although this works it changes the last value to a zero regardles of what I originally typed in. Any help would be much appreciated.

 

[dangre]

I think I have a solution for you, but I'll need your list of credit card numbers to demonstrate.

 

[PaddyD]

"...but I'll need your list of credit card numbers to demonstrate"

I don't see why

 

[dangre]

just a joke

 

[mgauci]

On 2002-11-07 22:00, dangre wrote:
just a joke

I don’t have any particular numbers that I’m working on I just wanted a general solution to the problem as I’m working on a project at work where this formatting would help. If you do have a solution it should be able to be tried on the 16 digit number 1234567812345678.
If you seriously have a solution I would appreciate your help.

 

[Joe Was]

One way is with one cell formated as text, say cell "A1" then in any other cell use:

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

This will return the string as formated by:
####-####-####-####
in the cell with the above formula.

Excel is limited to 15 numbers in a cell so unless you use some sort of text function, your out of luck.

I think a macro using a text box might work if you want to go that route? JSW

 

[cgreen]

Go to format cell, click on the number tab, then custom, in the type box enter ####-####-####-####, press OK.

Hopefully this will work for you.

 

[PaddyD]

On 2002-11-07 22:19, cgreen wrote:
Go to format cell, click on the number tab, then custom, in the type box enter ####-####-####-####, press OK.

Hopefully this will work for you.

as Joe pointed out, excel's decimal precision is limited to 15 digits. Numbers entered with > 15 digits will have the remaining digits 'converted' to zeros, so

####-####-####-####

on 1234567812345678

will produce:

1234-5678-1234-5670.

As Joe suggested, the only way to avoid this & keep the values in the cell will be with a bit of vba that takes the value as text & splits it up.

paddy

 

[Joe Was]

Try this macro. It asks the user for a card number then adds the formated number to cell "A1" on the sheet of the sheet module used. JSW

Sub myCard()
'Go's in the Sheet Module of operation.
myNum = Application.InputBox(prompt:="Enter your card number: ", Type:=2)
[A1].Value = Format(myNum, "####-####-####-####")

End Sub

 

[Joe Was]

This is event code to auto-format cell "A2" to a ####-####-####-#### formated string, after enter is pressed or a new cell is selected. JSW

Private Sub Worksheet_Change(ByVal Target As Range)
'Go's in the Sheet Module of operation.
'Will automatically convert any number in cell "A2"
'to a formated string!
Dim MyString
If [A2] <> "" Then
Range("A2").Select
Selection.NumberFormat = "@"
MyString = [A2].Value
[A2].Value = Format(MyString, "####-####-####-####")
Else
Exit Sub
End If
End Sub