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.

Try this

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

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



Gord Dibben MS Excel MVP


On Mon, 4 Feb 2008 12:19:05 -0800, Murph
wrote:

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.

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

On Mon, 4 Feb 2008 12:19:05 -0800, Murph
wrote:

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