masking with symbol xxxx

[oldLearner57]

hi community

kindly can anybody assist me in these task...

i hv following numbers sequence and wanted to mask the middle number with
symbol xxxx

example:

number: to masked

4556-1234-2345-1234 to 4556-xxxx-xxxx-1234

0377-0123-1234-1234 to 0377-xxxx-xxxx-1234

082-012345-1 to 082-xxxxxx-1


what i did was i use the mid function to extract and later do concatenating
of cells, is there any way that can help me to improvise the masking ??

&

and for the 2nd set of number, when I use concatenation, the zero digit in
front does not appear.



thanks community for the kind assistance given

 

[Rick Rothstein \(MVP - VB\)]

When you say "masking", do you mean you are physically changing the value in
the cell so that the original value no longer exists? Or are you just
placing the "mask" in a separate cell while retaining the original value in
the original cell? Also, are you allowed to use VB code (macros or UDF) in
your workbook?

Rick

 

[oldLearner57]

hi

the set of number will be change to

example

4556-1234- 2345-1234 to 4556-xxxx-xxxx-1234

and the rest similar format

is there any simple way besides doing macro?

much appreciated for the assistance



thanks community

 

[Rick Rothstein \(MVP - VB\)]

If you want to change the contents of a cell that is not the result of a
formula, then no, you will have to use VBA. A cell can either contain data
or a formula, but not both. If you are manually putting those values into
the cell, then there cannot be a formula in that cell too. Here is a macro
that will change all values in a selection the way you asked for...

Sub ReplaceWithXs()
Dim X As Long
Dim R As Range
Dim Parts() As String
For Each R In Selection
Parts = Split(R.Value, "-")
For X = 1 To UBound(Parts) - 1
Parts(X) = String(Len(Parts(X)), "X")
Next
R.Value = Join(Parts, "-")
Next
End Sub

Rick

 

[Teethless mama]

=LEFT(A1,FIND("-",A1))&SUBSTITUTE(REGEX.SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1,"-",REPT("
",99)),6,(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))*99)),"[0-9]","x"),"
","-")&"-"&TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99))