Unfortunately i cannot do that. I have hundreds of different old paycodes to convert, hence the lookup. The 2nd and 3rd letters change and are not just A, P or R. Its only the 3rd letters that reference a "P" i need to convert.



So what i am reying to say is;

If the ref cell contains a P in the 3rd position vse the lookup. If not, just stick with the existing reference cell.

So i added another code for reference sake.

Thanks,
Jon

Attached Files

excel help.xlsx (78.4 KB, 8 views)

In that case, my first formula should work unless I'm missing something.

=If(Mid(D19,3,1)="P",Left(D19,2) & "R" & Right(D19,3),D19)

To whit, if the third character is P, then use the first two original characters, and R, and the last three characters i.e. only change the third character to R if it originally P.

No lookup needed. Simple replacement schema.

Ok great. I have run that and got all the right results.

Really appreciate your help on this.

Thanks Gebobs.