replace value

[clares]

Hi All

Is there a way that I can take a cell value and replace the first two characters with two new characters., for example my current value is 04XXXX and I would like to replace the 04 with 05XXXX, however I cant use the find and replace feature because the XXXX part of the contains 0's aswell and it wants to change them aswell!! I have one column with about 5k rows.

Any help would be much appreciated.

Kind Regards

Peter

 

[jindon]

Hi
try

="05"&MID(A1,3,9^9)

hope this helps

jindon

 

[howzat]

or:

=REPLACE(A1,1,2,"05")

 

[Harvey]

this adds one to the number of the first two digits whenever they are "04". Change it to fit your needs.

=IF(LEFT(A1,2)="04",TEXT(LEFT(A1,2)+1,"00") & RIGHT(A1,LEN(A1)-2),A1)