replace last four of ssn with xxxx

[mary s]

How do I replace the last four of an ssn with xxxx? I tried:
Insert > function
Old text > A1 (or whatever start cell)
Start num > 8
Num char > 4
New text xxxx

The end result I want is 123-45-xxxx but every time i try to copy the
formula down it alters the successive ssns. Any ideas?

[dlw]

how about: =left(a1,7)&"xxxx"

"mary s" wrote:

> How do I replace the last four of an ssn with xxxx? I tried:
> Insert > function
> Old text > A1 (or whatever start cell)
> Start num > 8
> Num char > 4
> New text xxxx
>
> The end result I want is 123-45-xxxx but every time i try to copy the
> formula down it alters the successive ssns. Any ideas?
>

[Jim May]

=LEFT(A1,LEN(A1)-4)&"xxxx"


"mary s" wrote:

> How do I replace the last four of an ssn with xxxx? I tried:
> Insert > function
> Old text > A1 (or whatever start cell)
> Start num > 8
> Num char > 4
> New text xxxx
>
> The end result I want is 123-45-xxxx but every time i try to copy the
> formula down it alters the successive ssns. Any ideas?
>

[mary s]

Is there any way I can lock that formula so that when I copy it down a column
of SSNs the following SSNs don't change?

"dlw" wrote:

> how about: =left(a1,7)&"xxxx"
>
> "mary s" wrote:
>
> > How do I replace the last four of an ssn with xxxx? I tried:
> > Insert > function
> > Old text > A1 (or whatever start cell)
> > Start num > 8
> > Num char > 4
> > New text xxxx
> >
> > The end result I want is 123-45-xxxx but every time i try to copy the
> > formula down it alters the successive ssns. Any ideas?
> >

[David Biddulph]

"mary s" <[email protected]> wrote in message
news:[email protected]...
> "dlw" wrote:
>> "mary s" wrote:
>>
>> > How do I replace the last four of an ssn with xxxx? I tried:
>> > Insert > function
>> > Old text > A1 (or whatever start cell)
>> > Start num > 8
>> > Num char > 4
>> > New text xxxx
>> >
>> > The end result I want is 123-45-xxxx but every time i try to copy the
>> > formula down it alters the successive ssns. Any ideas?

>> how about: =left(a1,7)&"xxxx"

> Is there any way I can lock that formula so that when I copy it down a
> column
> of SSNs the following SSNs don't change?

You're not copying the formula down column A, are you? If your existing
SSNs are in column A, put your formula in column B (or any convenient empty
column). If, when you've done that, you want to replace the old SSNs with
the new ones, you can cut, then paste special/ values.
--
David Biddulph

[mary s]

Yeah. I was creating the formula and copying it down the same column. They
all work once I enter them in a new blank column and copy down. Thanks
everyone for your help!

"David Biddulph" wrote:

> "mary s" <[email protected]> wrote in message
> news:[email protected]...
> > "dlw" wrote:
> >> "mary s" wrote:
> >>
> >> > How do I replace the last four of an ssn with xxxx? I tried:
> >> > Insert > function
> >> > Old text > A1 (or whatever start cell)
> >> > Start num > 8
> >> > Num char > 4
> >> > New text xxxx
> >> >
> >> > The end result I want is 123-45-xxxx but every time i try to copy the
> >> > formula down it alters the successive ssns. Any ideas?
>
> >> how about: =left(a1,7)&"xxxx"
>
> > Is there any way I can lock that formula so that when I copy it down a
> > column
> > of SSNs the following SSNs don't change?
>
> You're not copying the formula down column A, are you? If your existing
> SSNs are in column A, put your formula in column B (or any convenient empty
> column). If, when you've done that, you want to replace the old SSNs with
> the new ones, you can cut, then paste special/ values.
> --
> David Biddulph
>
>
>