Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
16 digit numbering
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). |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
16 digit numbering
The credit numbers numbers should be defined/imported as TEXT not NUMBER to
avoid loosing the last digit. Format cell as TEXT. "Onesimus" wrote: 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). |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
16 digit numbering
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
16 digit numbering
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 "Onesimus" wrote: 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). |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
16 digit numbering
Onesimus wrote:
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
16 digit numbering
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" wrote in message ... Onesimus wrote: 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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
16 digit numbering
JoAnn Paules [MVP] wrote:
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
16 digit numbering
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/s...toryId=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 "JoAnn Paules [MVP]" wrote in message ... 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" wrote in message ... Onesimus wrote: 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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
16 digit numbering
David McRitchie wrote:
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/s...toryId=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert 2 digit month to 4 digit years and months | Excel Worksheet Functions | |||
Sorting a six digit number by terminal digit | Excel Worksheet Functions | |||
Color a single digit in a mult-digit number cell | Excel Discussion (Misc queries) | |||
Tell users how to sort 5 digit and 9 digit zipcodes correctly aft. | New Users to Excel | |||
When we enter a 16 digit number (credit card) the last digit chan. | Excel Discussion (Misc queries) |