Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conundrum
On 21/07/2007 20:03:59, JMB wrote:
That is much nicer. I agree. Thanks to all. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF conundrum | Excel Worksheet Functions | |||
Help!! NEW Conundrum for stats!? | Excel Discussion (Misc queries) | |||
VBA conundrum | New Users to Excel | |||
ISERROR Conundrum | Excel Worksheet Functions | |||
ISERROR Conundrum | Excel Worksheet Functions |