Replacing values with x's and showing last 4 digits.

[errollflynn]

Hello,

I am creating a report that has banking information and for obvious reasons, I do not want to show the full account numbers. As account numbers vary in length, I'm trying to figure out a way to display account number 123456789 as xxxxx6789. The challenge is in figuring out a replace or substitute formula that leaves the last four digits of a character string that varies in length untouched.

Thank you.

 

[JEC]

For example:

=LET(x,RIGHT(A1,4),REPT("x",LEN(SUBSTITUTE(A1,x,"")))&x)

 

[errollflynn]

Works perfectly! Thank you!

 

[JEC]

You're welcome!

 

[Peter_SSs]

Works perfectly! Thank you!

Are you sure?
Perhaps your account numbers would not allow this, but try that with account number 44444444 or 123451234


In any case, if I understand correctly, simpler would be

=REPT("x",LEN(A1)-4)&RIGHT(A1,4)