Hey there,
I'm trying to figure out how to create a function that replaces the sequence " CR " with " CIR ". Here's the catch - there are a number of " CR " 's that I want to preserve; it is only the " CR " 's whose first character is located 6, 7, or 8 places from the right that I want to replace.
I've managed to replace all the " CR " 's that are on the end using the Right function:
=IF(RIGHT(H2,4)=" CR ",REPLACE(H2,SEARCH(RIGHT(H2,4),H2,4),5," CIR "),H2)
Now I would like to do this with the Mid function for those located 6,7,and 8 places from the right. However, they haven't worked as planned:
=IF(MID(H2,LEN(H2)-6,4)=" CR ",REPLACE(H2,SEARCH(MID(H2,LEN(H2)-6,4),5," CIR ")H2)
Can anybody help me on this?
Thanks!
Bookmarks