remove and replace a digit...

On Mon, 4 Feb 2008 12:19:05 -0800, Murph <Murph RemoveThis @discussions.microsoft.com>
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

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" <Murph.DeleteThis@discussions.microsoft.com> wrote in message
news:3F5D6C69-CE7F-4155-A741-8613F04E114B@microsoft.com...
>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.