View Single Post
 
Old 08-31-2017, 12:57 PM
gebobs gebobs is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

Quote:
Originally Posted by jonnie_rc View Post
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.
Reply With Quote