Conundrum

[Saxman]

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.

 

[David McRitchie]

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

 

[Guest]

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]

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.

 

[Ragdyer]

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)

 

[Guest]

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)

 

[Guest]

That is much nicer.

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)

 

[Saxman]

That is much nicer.

I agree. Thanks to all.