Find & Replace With A Twist

[VitesseCarOwner]

I have two columns of data 'A' and 'M'. In column 'A' where the first three characters are 1Q_ I Need to replace with "1QT_" but only if the corresponding cell in column 'M' has the value "Day D". Can anyone help? In the table below the 4th entry would require updating.

Column A	Column M
1Q_ABC	Day B
2Q_ABC	Day D
3Q_HGF	Day C
6Q_POI	Day B
1Q_ABC	Day D
2Q_ABC	Day C
3Q_HGF	Day B

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[godsaaint]

Try: =IF(B1="Day D",SUBSTITUTE(A1,"1Q_","1QT_"),A1)

 

[AliGW]

I think this requires a VBA solution, Godsaint: your formula would have to sit in another column, whereas the OP has asked for a solution to "replace" what is in column A.

 

[BarryL]

this should do the trick

Sub test()
Dim add As String, addr As String
Let add = "A1:A" & Range("A" & Rows.Count).End(xlUp).Row
Let addr = "M1:M" & Range("M" & Rows.Count).End(xlUp).Row
Range(add).Value = Evaluate("IF(" & addr & "=""Day D"",SUBSTITUTE(" & add & ",""1Q_"",""1QT_"")," & add & ")")
End Sub

 

[VitesseCarOwner]

Thanks, both methods work for me.

 

[BarryL]

Glad you sorted it.