# How to censor account numbers based on how many characters are in the account number

1. ## How to censor account numbers based on how many characters are in the account number

Hello Excel Guru's

My job is to email out funding notifications to our clients who do not like their banking account information out in the open. So for example if someone's account number is 4587631234 we would instead write ******1234. What I am looking for is a formula that will censor every digit except for the last 4 when the account number has 7 or more all but the last three when there are 6 characters and all but the last two when there are 5 or less characters (****1234,
***9567,***567,***88 for example). I have attached a dummy excel file to test anyone’s ideas for what type of formula to use. Thanks in advance to any and all help

CENSOR PROBLEM.xlsx

2. ## Re: How to censor account numbers based on how many characters are in the account number

In an effort to keep the formula from having multiple If statements, I would suggest the solution in the attached file.
You can simply change the values in H3:I5 if something changes.

3. ## Re: How to censor account numbers based on how many characters are in the account number

I knew there was a clean way without the table. Here is an alternative, stand-alone formula:
Formula:
`Please Login or Register  to view this content.`

4. ## Re: How to censor account numbers based on how many characters are in the account number

``Please Login or Register  to view this content.``

5. ## Re: How to censor account numbers based on how many characters are in the account number

Hi Mcorydon,

Try this formula

=IF(A1>1000000,(CONCATENATE("****",RIGHT(A1,4))),(IF(A1>100000,(CONCATENATE("***",RIGHT(A1,3))),(IF(A1>10000,(CONCATENATE("***",RIGHT(A1,2))),"")))))

So if it is a 7 digit number (ie above 1,000,000) then it will put **** + the 4 digits from the right of it, if not it will see if its a 6 digit number and if not a 5 digit, if its not then it will put blank.

Hope this helps

6. ## Re: How to censor account numbers based on how many characters are in the account number

You are an unbelivable help, also do you know of a way to make an excel dropbox act like google would so that i could type part of a client and thats what would show up? does that make sense?

7. ## Re: How to censor account numbers based on how many characters are in the account number

The feature is called "Auto-Complete", and I'm fairly certain there is no way to have it work the way you described in excel.
The only time it works is in a continuous column of data (if you type September in B2, and go to type September again in B3 it will Auto-Complete).

8. ## Re: How to censor account numbers based on how many characters are in the account number

Hi Mcorydon

Another version!
Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)