How do I display only the last four digits of Credit Card numbers in Excel?

Question

I tried: =CONCATENATE(REPT("XXXX-",3), RIGHT(H3,4)), but I'm given an error message every time I try. It says the cell is a circular reference which I didn't create. How do I rectify this, does anyone know a quick, effective way? (Simple would be nice too if possible)

Answer 1

=REPT("XXXX-",3) & RIGHT(H3,4)

You can use & to join bits of text instead of using CONCATENATE, but the formula you have is perfectly valid.

If you are getting a circular reference, then you have probably put the formula in cell H3.  A formula cannot refer to the same cell it is in.  You will have to have your original credit card number in a different cell.

Unfortunately, Excel doesn't come with any custom format tricks (that I know of) to format text, so you are probably stuck with having two cells - one with the full number, and one with the formatted (via formula) number.

Answer 2

=CONCATENATE(REPT("XXXX-",3),RIGHT(A2,4)...

Assuming the credit card number was in cell A2, this formula works fine and I got no error.  My output was:

Data in Cell A2 was: 1234567890123456

XXXX-XXXX-XXXX-3456

Of course you'll run into problems with 16 digit numbers with Excel "zero"ing out your 16th digit. Add a " ' " before the number to force display as string.

Answer 3

You reversed it. Right then Rept.

See link below.