combine nested IF function with REPLACE function

Question

i put three IF in my formula while combining it with MID &  REPLACE functions ;but in true section the result is wrong .

the formula is here:

=IF(MID(I6,3,2)="09",REPLACE(I6,3,9,"01"),IF(MID(I6,3,2)="11",REPLACE(I6,3,11,"03"),IF(MID(I6,3,2)="12",REPLACE(I6,3,12,"07"),I6)))

Note that each cell contain 7 character.

Answer 1

Hi,

I am not sure of what you want to do, but try this

=IF(MID(I6,3,2)="09",REPLACE(I6,3,2,"01"),IF(MID(I6,3,2)="11",REPLACE(I6,3,2,"03"),IF(MID(I6,3,2)="12",REPLACE(I6,3,2,"07"),I6)))

I have underlined the changes I have made.

Hope this helps.

Answer 2

Hi,

Try this

=IF(MID(I6,3,2)="09",REPLACE(I6,3,2,"01"),IF(MID(I6,3,2)="11",REPLACE(I6,3,2,"03"),IF(MID(I6,3,2)="12",REPLACE(I6,3,2,"07"),IF(MID(I6,2,1)="6",REPLACE(I6,2,1,"5"),I6))))

Hope this helps.

6094032
6124033
5114034
5124035

Hi ; numbers are illustrated in the above table . I want to change some numbers in second; third & forth character where condition is established.

so should i use OR & AND function and how can i use IF function in this problem?

Answer 3

Hi,

Try this

=IF(MID(I6,3,2)="09",REPLACE(I6,3,2,"01"),IF(MID(I6,3,2)="11",REPLACE(I6,3,2,"03"),IF(MID(I6,3,2)="12",REPLACE(I6,3,2,"07"),IF(MID(I6,2,1)="6",REPLACE(I6,2,1,"5"),I6))))

Hope this helps.

Answer 4

thanks for your answer Mr Mathur

so how can i set a new condition on the second character changing numbers simultaneously.

 i want to add this formula  :  IF(MID(I6,2,1)="6",REPLACE(I6,2,1,"5") to last condition :

=IF(MID(I6,3,2)="09",REPLACE(I6,3,2,"01"),IF(MID(I6,3,2)="11",REPLACE(I6,3,2,"03"),IF(MID(I6,3,2)="12",REPLACE(I6,3,2,"07"),I6)))