remove and replace a digit...

  • Thread starter Thread starter Murph
  • Start date Start date
M

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.
 
Try this

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

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



Gord Dibben MS Excel MVP
 
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.
Click to expand...

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
 
Back
Top