16 digit numbering

[Onesimus]

I work for a charity. We have an agency which collects new donors for us. On
a weekly basis, this agency sends me a spreadsheet file containing data about
the new donors which I import into our database.

One item of data they collect is credit card numbers as credit cards are
used to pay donations. Unfortunately, given that Mastercard and Visa card
numbers are 16 digits long, the credit card data we get back from the agency
always has the last value (the 16th number) as a zero. I am assuming that
Excel is accurate to 15 digits only. This means that, 9 out of 10 times, I
have to manually change the last digit in the database after it has been
imported from Excel.

Is there any way to get Excel to show a 16 digit number correctly and/or is
it possible to display a 16 digit number in 4 blocks of 4 as in 1234 1234
1234 1234 (preferably).

 

[Toppers]

The credit numbers numbers should be defined/imported as TEXT not NUMBER to
avoid loosing the last digit.

Format cell as TEXT.

 

[MartinW]

Hi Onesimus,

As Toppers said either convert the info to text as you are
importing it or pre-format your cells as text before you import.

To get the numbers into blocks of four you can insert a helper
column next to your credit card numbers and insert a formula
like this into the first cell then drag it down as far as is needed.

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

If your data is not that uniform there are other approaches
along the same lines.

HTH
Martin

 

[JLatham]

If you need a way to help assure that the card numbers provided to you are
valid and weren't typo'd or corrupted during the data transmission, you can
put this code into your workbook and then add a formula in a new column to
tell you if they are at least valid CC numbers. Assuming that our card
numbers are entered into column A beginning in row 2, and that column B is
available for use, you could put a formula like this into column B:
=IF(CheckCard(A2),"Valid","Invalid Card")

As noted by others - the card number should be Text, they can even have the
dashes or spaces between groupings and the function will return proper
indicator.

Here's the code:

Function CheckCard(CCNumber As String) As Boolean
Dim CCLength As Integer
Dim Counter As Integer
Dim TmpInt As Integer
Dim TestResult As Integer

Counter = 1
'remove non-numeric characters
CCNumber = CleanUpEntry(CCNumber)
CCLength = Len(CCNumber)
Do While Counter <= CCLength
TmpInt = CInt((Mid(CCNumber, Counter, 1)))
If IsEven(CCLength) Then
'checks for 16-digit entries
If Not IsEven(Counter) Then
TmpInt = TmpInt * 2
If TmpInt > 9 Then TmpInt = TmpInt - 9
End If
Else
'checks for 13 and 15 digit cards
'as Diners Club and American Express
If IsEven(Counter) Then
TmpInt = TmpInt * 2
If TmpInt > 9 Then TmpInt = TmpInt - 9
End If
End If
TestResult = TestResult + TmpInt
Counter = Counter + 1
Loop
TestResult = TestResult Mod 10
CheckCard = TestResult = 0

End Function

Private Function CleanUpEntry(InputNumber As String) As String
Dim LC As Integer
Dim lsTemp As String
Dim lsChar As String

For LC = 1 To Len(InputNumber)
lsChar = Mid(InputNumber, LC, 1)
If IsNumeric(lsChar) Then lsTemp = lsTemp & lsChar
Next LC
CleanUpEntry = lsTemp
End Function

Private Function IsEven(anyNumber As Integer) As Boolean
IsEven = CBool((anyNumber Mod 2) = 0)
End Function

 

[gls858]

I work for a charity. We have an agency which collects new donors for us. On
a weekly basis, this agency sends me a spreadsheet file containing data about
the new donors which I import into our database.

One item of data they collect is credit card numbers as credit cards are
used to pay donations. Unfortunately, given that Mastercard and Visa card
numbers are 16 digits long, the credit card data we get back from the agency
always has the last value (the 16th number) as a zero. I am assuming that
Excel is accurate to 15 digits only. This means that, 9 out of 10 times, I
have to manually change the last digit in the database after it has been
imported from Excel.

Is there any way to get Excel to show a 16 digit number correctly and/or is
it possible to display a 16 digit number in 4 blocks of 4 as in 1234 1234
1234 1234 (preferably).

Just a word of caution here. Storing credit card info requires
compliance to the PCI Data Security Standards. Non compliance leads
to a risk you may not want to take. Do some searching on PCI Data
Security and you'll see what I mean.

gls858

 

[JoAnn Paules [MVP]]

Thank you. I was thinking that I'd be hesitant to hand over my credit card
number to an organization that just kept my number in Excel with no security
measures.

--

JoAnn Paules
MVP Microsoft [Publisher]

~~~~~
How to ask a question
http://support.microsoft.com/KB/555375

 

[gls858]

Thank you. I was thinking that I'd be hesitant to hand over my credit card
number to an organization that just kept my number in Excel with no security
measures.

The sad thing is you might never know. Sadder still is that people
storing them don't know they're doing anything wrong. If they ever
get hacked they're in for a rough time. The CC will go after them for
every penny. My advice to anybody that's thinking of storing credit card
info is to talk to your bank. They can set up a merchant account and
provide software to transmit your transactions and store the info
in a secure manner.

gls858

 

[David McRitchie]

What about giving your card number and other
information over to anyone that simply walks by you
a little further away than a pickpocket. I guess we'll
find out soon if the "extra" safeguards work, or what
exactly is stored.

http://www.npr.org/templates/story/story.php?storyId=4664479

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thank you. I was thinking that I'd be hesitant to hand over my credit card
number to an organization that just kept my number in Excel with no security
measures.

--

JoAnn Paules
MVP Microsoft [Publisher]

~~~~~
How to ask a question
http://support.microsoft.com/KB/555375

Just a word of caution here. Storing credit card info requires compliance
to the PCI Data Security Standards. Non compliance leads
to a risk you may not want to take. Do some searching on PCI Data
Security and you'll see what I mean.

gls858

 

[gls858]

What about giving your card number and other
information over to anyone that simply walks by you
a little further away than a pickpocket. I guess we'll
find out soon if the "extra" safeguards work, or what
exactly is stored.

http://www.npr.org/templates/story/story.php?storyId=4664479

Snip<

Nice link David. I knew this type of card was coming just hadn't heard
any details. Actually if these are implemented correctly, they could be
more secure than the cards now. Time will tell. I won't be one of the
first ones to have one!

gls858