data masking help

dessim

New Member
Joined
Aug 4, 2019
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
Evening ppl,

Is there any way I could directly partially mask user input within the same input cell i?
Meaning if user will to key in S12345678Z within A1 cell, it will auto mask to S********Z within A1 cell itself.

The only method I have successfuly done is 1 cell for User Input ( e.g A1) then masked result cell (E,g A2 using formula like =LEFT(A1,1)&"****"&RIGHT(A1,4)

Seek some guidance here.,
Thank you very much.
 
The solution above in post #3 will completely mask the contents of the cell to anyone who sees it (you will not see the first and last character), but the contents will still be what the user typed in. It sounds like that is what you want.

If it is not then you need to thoroughly explain how you want to use this, who can see what, and how you will use the data.

Basically, this is about a visitor form whereby requestor will have fill up with some particular. Once it’s approved it will be sent back to requestor. There are few key data that is required, one of them is Foreign Identification Number (FIN).

Thus, as mentioned earlier on I created 2 cells under FIN.

I set A1 as input cell ( custom code ;;;which it will not display any input that is key in within the cell ( but of cos will still appear at the formula bar) , then at another cell A2, I used formula =LEFT(A1,1)&"****"&RIGHT(A1,4) in order to achieve the masked result.

The entire sheet will be protected with password (Hidden option & locked option checked under Protection tab) before its send back to requestor. A1 cell data will thus be protected, not displayed / will not be printed even if form was to be printed out.

Foreign Identification Number (FIN)
A1 --------------| A2
S1234567Z -----| S*****567Z

Reason for doing so

  • only A2 (masked data) will be display when print.
  • A1 data will still be retained, this will help when tracing of any particular visitor is required. When using masked data to search, it tends to generate a lot of irrelevant results as compare with a search on full FIN numbers.
Although I managed to achieved what I wanted. I somehow felt the form designed was kinda of awkward, with A1 cell appearing “invisible”, A2 cell with masked data. Thus, this question arises, can I combine both “functionality” with a single cell- retaining full data, but only display partial data on screen.

I understand using of formula might be impossible. Is there any VBA coding for such situation ?
If not, I guess I have to stick with this 2 cell concept deisgn of the form.

Thanks alot.
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
can I combine both “functionality” with a single cell- retaining full data, but only display partial data on screen.
The closest you can come just by formatting is what lrobbo314 suggested, which will show only stars and no partial data. The only way to do exactly what you want is to have the full data stored in one place and displayed masked in another place. There are different ways to do that, and they've already been discussed in this thread.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top