If I understand you correctly, if the middle 3 numbers in one of the part numbers in the Substitute tab is 555, you want to replace the entire sequence with something new. Here is how I would do it, starting from the first row, then auto filling down: =IF(MID(A4,5,3)="555",REPLACE(A4,1,11,9999),A4) This means "If the middle sequence of A4 is 555, replace the entire cell with 9999. Otherwise, replace the entire contents with itself."
Хобби
What about doing this type of thing to replace the whole cell?
If I understand you correctly, if the middle 3 numbers in one of the part numbers in the Substitute tab is 555, you want to replace the entire sequence with something new. Here is how I would do it, starting from the first row, then auto filling down:
=IF(MID(A4,5,3)="555",REPLACE(A4,1,11,9999),A4)
This means "If the middle sequence of A4 is 555, replace the entire cell with 9999. Otherwise, replace the entire contents with itself."