masking with symbol xxxx

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" <oldLearner57.TakeThisOut@discussions.microsoft.com> wrote in message
> news:833DFD38-E14D-470F-82C3-296DF66AAF01@microsoft.com...
> > 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" <oldLearner57.TakeThisOut@discussions.microsoft.com> wrote in message
news:3B523F84-4EEC-4CA9-8E68-BA7140CED640@microsoft.com...
> 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" <oldLearner57.TakeThisOut@discussions.microsoft.com> wrote in message
>> news:833DFD38-E14D-470F-82C3-296DF66AAF01@microsoft.com...
>> > 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(SUBSTITUTE(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" <oldLearner57.RemoveThis@discussions.microsoft.com> wrote in message
> > news:833DFD38-E14D-470F-82C3-296DF66AAF01@microsoft.com...
> > > 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
> >
> >