You could leave the original formulas as they are and just change this:

=IF(AND(B1=1,B1<=9),CHOOSE(B1,9,7,5,0,0,0,0,0,0,) ,0)

to this

=IF(ISNUMBER(B1+0),LOOKUP(B1+0,{1,2,3,4;9,7,5,0}), 0)

"Ragdyer" wrote:

If your format is always going to be the same (2 numbers followed with a
letter), simply add the double unary to the beginning of Max's first 2
formulas, so that *they* return numbers instead of text.

=--MID(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""),LEN(SUBSTITUTE(SUBSTITUTE($
A1,"-",""),"/",""))-(3-COLUMNS($A:A)),1)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Saxman" wrote in message
...
Earlier today Max kindly worked out a function for me as follows:-

The following data is in cell A1.
0/12-F

The functions below placed in cells B1, C1, D1 give the values 1, 2, F.



=MID(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""),LEN(SUBSTITUTE(SUBSTITUTE($A1
,"-",""),"/",""))-(3-COLUMNS($
A:A)),1)


=MID(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""),LEN(SUBSTITUTE(SUBSTITUTE($A1
,"-",""),"/",""))-(3-COLUMNS($
A:B)),1)


=MID(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""),LEN(SUBSTITUTE(SUBSTITUTE($A1
,"-",""),"/",""))-(3-COLUMNS($
A:C)),1)

I now need to convert the extracted data, 1, 2, F to other values. I have
a
function for this in cell E1, namely,
=IF(AND(B1=1,B1<=9),CHOOSE(B1,9,7,5,0,0,0,0,0,0,) ,0)

The above should give the value 9 with 1 in cell B1, but it doesn't. Maybe
it's because it is piggy-backed onto another function?

When I manually type 1 into cell B1, I get the correct output in cell E1,
i.e.,9.

I have tried formatting the input and output cells to text, number etc.,
but
it makes no difference.