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

:)


--
oldLearner57

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" wrote in message
...
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

:)


--
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
--
oldLearner57


"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

:)


--
oldLearner57

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


"oldLearner57" wrote in message
...
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
--
oldLearner57


"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

:)


--
oldLearner57

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


"oldLearner57" wrote:

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
--
oldLearner57


"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

:)


--
oldLearner57