I have a table of letters which correspond to single it numbers. The letters
a thru i correspond to 1 thru 9, and j thru r correspond to 1 thru 9 as
well.
I get a table of multi digit alphnumerics in the form 4567C or 398g or
28754n, etc.
I place the letters and corresponding digits in a lookup table. I replace
the letters at the end with the corresponding number as defined above with
the formula
=SUBSTITUTE(D2,RIGHT(D2,1),VLOOKUP(RIGHT(D2,1),$A$ 1:$B$19,2))
Works fine.
What I cant figure out is if the letter is between j thru r, I need to must
convert the formula result to a negative number.
For example, 458b becomes 4582, while 458k becomes -4582.
Have tried IF, but cant seem to get the syntax correct. Possibly some sort
of array formula might help, but cant get that either.
Any suggestions???
Thanks, Marsh