remove and replace a digit...

[Murph]

I am trying to remove a digit and replace it with another digit.

I.E.: A1 has '2001' in the cell... I want to change that to '9001'.

I have dabbled with =left (a1,3) to remove the '2' but cannot figure out how
to place the 9 in.

Thanks in advance.

 

[Gord Dibben]

Try this

=SUBSTITUTE(A1,"2","9")*1

=REPLACE(A10,1,1,"9")*1 which is shorter<g>



Gord Dibben MS Excel MVP

 

[Bernard Liengme]

If you do not want to do math use: =9&RIGHT(A1,3)

 

[Ron Rosenfeld]

I am trying to remove a digit and replace it with another digit.

I.E.: A1 has '2001' in the cell... I want to change that to '9001'.

I have dabbled with =left (a1,3) to remove the '2' but cannot figure out how
to place the 9 in.

Thanks in advance.

Lots of ways but you don't give enough information.

For example:

=SUBSTITUTE(A1,2,9,1)

will replace the first 2 in a string with a 9.

=REPLACE(A1,1,1,9)

will replace the first character in the string with a 9.

=VLOOKUP(--LEFT(A1,1),$I$1:$J$10,2,FALSE)&MID(A1,2,255)

Can do a variable replace on the first character where the replacement table is
in I1:J10 and looks like:

1 0
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
0 1


So it replaces a number in Column I with the corresponding number in Column J.
--ron