Quote:
Originally Posted by jonnie_rc
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.
|
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.