Cell formatting for 16 digit numbers

mgauci

New Member
Joined
Nov 6, 2002
Messages
21
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.
 

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
Joined
May 27, 2002
Messages
134
I think I have a solution for you, but I'll need your list of credit card numbers to demonstrate.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"...but I'll need your list of credit card numbers to demonstrate"

I don't see why :)
 

mgauci

New Member
Joined
Nov 6, 2002
Messages
21

ADVERTISEMENT

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
Joined
Feb 19, 2002
Messages
7,539
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
Joined
Aug 14, 2002
Messages
291

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
Joined
Feb 19, 2002
Messages
7,539
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top