broken code

[Rebellion34]

Ok I am calling on the services of the code GURU's once again. I have written a formula for a project I am doing but have now hit a small roadblock. the code is:
IF(A2<"V111V9H7001E290",A2,CONCATENATE("",CODE("a")-87,MID(A2,3,1))),SUBSTITUTE("a","10a","10/")
the purpose of this code is to pick out the third digit of V1A1V9H7001E290 and convert it to a # value where 1-9 = 1-9 and A-C = 10-12i can get the 3 to generate but neet to lose the original letter that now shows as 10A. can you guys show me where I am going wrong?

thanks

 

[AnotherHiggins]

OK, I'm confused....

First, just for future reference, this is a formula not code. "Code" in Excel generally refers to VBA (macros).

Now then, the formula you posted doesn't make any sense. The parentheses are wrong and it would give you an error if you tried to put that in a cell (with an "=" in front, of course). The Substitute section comes after you've already closed out the IF statement.

Also, [COLOR=blue white]CODE("a")-87[/color] will always evaluate to 10 since the "a" is fixed. Why even have it there?

The same goes for [COLOR=blue white]SUBSTITUTE("a","10a","10/")[/color], which will always evaluate to "a" since "10a" isn't found within the text-string "a," which, again, is fixed.

Do you have a working formula? If so, could you post that?

If you ARE talking about VBA code, please repost in forum707.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

[tt]
=IF(ISNUMBER(VALUE(MID(A2,3,1))),MID(A2,3,1),MATCH(MID(A2,3,1),{"A","B","C"},0)+9)
[/tt]
gets you 0 to 12.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rebellion34]

Skip,

that is exacly what i was looking for and i agree with anotherhiggins the formula i entered will not work and had come to that same conclusion as to the formula will always add the 10 and not replace the a as i had intended, i had worked out a new code that provided part of the desired effect, using Skips formula i can now work the rest of the formula out. anotheruser thank you for your advice and Skip thanks for the formula glad you worked out the method from my madness lol.

thanks all