How to mask accounts in excel sheet?

Im working for a bank where customer accounts with varied lengths need to masked in excel sheet.

Is there any macro or formatting method which could help me doing this?

Eg:

Cell No  Value:

A10      46579094628
A11      NL653912开发者_StackOverflow社区3747796621

This would turn to

A10      46XXXXXXX28
A11      NLXXXXXXXXXXXXXX21

I want to keep 1st 2 digits and last 2 or 3 digits intact. Please advise.

Who is going to be using this spreadsheet?

I ask because you can certainly create a new column with values computed by manipulating the text in the account-number column and you can get it to look exactly the way you want. But then you'll have to hide the original column. That may be inconvenient because:

1. Harder to maintain. Who/how are new accounts going to be added?
2. How do you know users won't just unhide the column? Seems like now you've got a password to manage.
3. Are you sure bank officials are ok with this?

Option two is to create the spreadsheet by manipulating account numbers from an export from a more secure DB so that they never make it into Excel. Then you don't have to worry about passwords, hidden cells, etc.

If you're looking for a way to create a separate column with the mask you can use the following:

=LEFT(<targetcell>,2)&REPT("X",LEN(<targetcell>)-4)&RIGHT(<targetcell>,2)

Just replace targetcell with the cell containing the account number.

I'd then copy the formula down for the full column; copy and paste the formula column "As Value" and delete the original account column to remove the sensitive information. (These steps could be entirely automated in VBA but this is an easy solution.)

Mask by

=right(sheet_org!a1a;2) & left("XXXXXXXXXXXXXXXX";len(sheet_org)-4) & left(sheet_org!a1,2)

if sharing without original content, copy and paste to new spreadsheet by macro.

or get external data in new workbook pointing at sheet with masked values.

regards, //t

If your value is in cell A10 then insert the following formula in any blank cells

=LEFT(A10,2)&REPT("X", LEN(A10)-4)&RIGHT(A10, 2)