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.