MS Excel has a 15 digit limit so that any digits you key in past the default limit are replaced with zeros. This can present a problem if you need to enter 16 digit credit card numbers in your spreadsheet.
If you key in 1234567890123456, what will appear in your spreadsheet is 1234567890123450.
To avoid losing digits that go beyond the 15 digit limit, you will need to format the cells as Text.
Alright that resolves the limit issue, but what if, for security, you would like to obscure all but the last few digits of the credit card number?
You can combine three of MS Excel’s functions to accomplish this, Concatenate, Right, and Rept.
The formula is below:
=CONCANTENATE(REPT(“***_”,3), RIGHT(B2,4))
REPT(****_”,3) – Repeats the ****_ string 3 times (first 12 digits obscured). If you prefer you can replace the _ with a blank space or omit it.
RIGHT(B2,4) returns the 4 right-most digits.
CONCATENATE joins the results from the previous two functions.
i.e. if cell B2 contains 1234567890123456. =CONCATENATE(REPT(“****_”,3),RIGHT(B2,4)) will result in
****_****_****_3456.