data masking help

[dessim]

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.

 

[6StringJazzer]

You can't store S12345678Z in a cell and also have it display in the same cell as S********Z . This is a complicated problem because if you mask the data for display, you still have to store the unmasked data someplace.

If you don't like your own formula solution, this is probably going to require VBA. What do you do with that data once the user has entered it? What is the level of security needed--is this data masked just for printing purposes, or is there the possibility of someone accessing the spreadsheet who is not authorized to view the unmasked data?

Also does this need to be masked as the user is typing, like a password, or only after the user has completed it and hit ENTER?

 

[lrobbo314]

This cell format will format the cell with all asterisks.

;;;**

 

[6StringJazzer]

This cell format will format the cell with all asterisks.

;;;**

It will not show the first and last characters. Also, it will fill the cell with "*" for its entire width regardless of the actual number of characters in the string.

 

[lrobbo314]

It will not show the first and last characters. Also, it will fill the cell with "*" for its entire width regardless of the actual number of characters in the string.

I'm aware of that. That's why I said, 'it will fill the cell with all asterisks'.

Another option would be to just color the characters in the middle to be colored white. So instead of asterisks, the middle characters would appear blank like 'S Z'

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Cells.Count = 1 Then
    For i = 2 To Len(Target.Value) - 1
        Target.Characters(i, 1).Font.ColorIndex = 2
    Next i
End If
End Sub

 

[6StringJazzer]

If you create another sheet (which could be hidden) to store the original value, this code will cop the value as entered to the other sheet then add the asterisks for display on the sheet where entered. In this case, "Data" is the codename of the other sheet.

The best solution will depend on how you are using this data after the user has entered it.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Cell As Range
   
   Application.EnableEvents = False
   
   For Each Cell In Target
      If Cell.Column = 1 Then
         Data.Cells(Cell.Row, Cell.Column) = Cell.Value
         Cell = Left(Cell, 1) & _
                  Application.WorksheetFunction.Rept("*", Len(Cell) - 2) & _
                  Right(Cell, 1)
      End If
    
   Next Cell

   Application.EnableEvents = True

End Sub

 

[dessim]

I'm aware of that. That's why I said, 'it will fill the cell with all asterisks'.

Another option would be to just color the characters in the middle to be colored white. So instead of asterisks, the middle characters would appear blank like 'S Z'

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Cells.Count = 1 Then
    For i = 2 To Len(Target.Value) - 1
        Target.Characters(i, 1).Font.ColorIndex = 2
    Next i
End If
End Sub

Thanks for the suggestion above.
Pardon my limited knowledge, cant seems to get the above "working"

The above VBA only require range to be set right ? for example A1-A9 are the input cell -->thus (Target, Range ("A1:A9")) ???

 

[dessim]

You can't store S12345678Z in a cell and also have it display in the same cell as S********Z . This is a complicated problem because if you mask the data for display, you still have to store the unmasked data someplace.

If you don't like your own formula solution, this is probably going to require VBA. What do you do with that data once the user has entered it? What is the level of security needed--is this data masked just for printing purposes, or is there the possibility of someone accessing the spreadsheet who is not authorized to view the unmasked data?

Also does this need to be masked as the user is typing, like a password, or only after the user has completed it and hit ENTER?

maybe I didnt states clearly.
Example:

I set A1 as input cell ( custom code ;; which mean it will not display any input key within, then at another cell A2, I used formula =LEFT(A1,1)&"****"&RIGHT(A1,4)

A1 A2
S1234567Z S*****567Z

This thought then came to me, whether A2 result can be achieve with 1 single cell, meaning once user key in at A1, it will auto becomes S****567Z at A1
but then the problem are:

1) how to simplify the design of my form, while still able to protect user impt information also if possible still able to retain orginial user input
2) does it require complicated VBA code if no existing formula can be apply...I know nuts abt VBA coding

.

 

[6StringJazzer]

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.

 

[Rick Rothstein]

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.

Assuming the reason the OP wants to mask the cell's content is to stop anyone from reading what is actually in the cell. Given that, the OP will have to lock the cell with that format and protect the sheet making sure to not allow the user to select locked cells (otherwise simply selecting the cell and looking at the Formula Bar would render the mask as useless).