# Cell formatting for 16 digit numbers

#### mgauci

##### New Member
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.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### dangre

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

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

I don't see why

just a joke

#### mgauci

##### New Member

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

##### MrExcel MVP
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

##### Active Member

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

Hopefully this will work for you.

##### MrExcel MVP
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.

#### Joe Was

##### MrExcel MVP
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

##### MrExcel MVP
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

Replies
4
Views
119
Replies
3
Views
159
Replies
4
Views
212
Replies
4
Views
309
Replies
10
Views
798

1,148,182
Messages
5,745,210
Members
423,933
Latest member
ankushmukherjee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back