Conundrum
 

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.

Conundrum
 

Hi John,
If the column is wide enough you would probably
notice that the data is left justified, which is a pretty
good hint that it is text. That is just the default you
can justify anything however you want.

Format the column as General (not Text)
then use the TrimALL macro on the column, see
http://www.mvps.org/dmcritchie/excel/join.htm

--
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Saxman" <john.h.williams wrote
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.

Conundrum
 

Likely due to MID returning a text "1" instead of numeric 1. You could try
using the double unary operator to coerce "1" to numeric value:

=IF(ISNUMBER(--B1),CHOOSE((--B1=1)*(--B1<=9)*B1+1,0,9,7,5,0,0,0,0,0,0),"Not
A Number")


"Saxman" wrote:

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.

Conundrum
 

On 21/07/2007 17:55:58, JMB wrote:
Likely due to MID returning a text "1" instead of numeric 1. You could try
using the double unary operator to coerce "1" to numeric value:

=IF(ISNUMBER(--B1),CHOOSE((--B1=1)*(--B1<=9)*B1+1,0,9,7,5,0,0,0,0,0,0),"Not
A Number")

That was the problem!
However, I have had to replace "not" with "0" so I can add the 3 columns, as
I was getting "#VALUE" returned in the non-numerical cells.

Thank you very much indeed.

Conundrum
 

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.

Conundrum
 

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.

Conundrum
 

That is much nicer.

"daddylonglegs" wrote:

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.

Conundrum
 

On 21/07/2007 20:03:59, JMB wrote:
That is much nicer.

I agree. Thanks to all.