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"

"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?

=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?

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?

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

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