How to output the left most cell?

  • Thread starter Thread starter Morrigan
  • Start date Start date
M

Morrigan

I have a row of cells, for instance A1:BZ1, which can be blank o
number. I am able to output the right most cell with the formula:

LOOKUP(9.99999999999999E+307,A1:BZ1)

Is there a similar formula that allows outputting the left most cel
that contains a number?


Thank you in advanced
 
I have a row of cells, for instance A1:BZ1, which can be blank or
number. I am able to output the right most cell with the formula:

LOOKUP(9.99999999999999E+307,A1:BZ1)

Is there a similar formula that allows outputting the left most cell
that contains a number?


Thank you in advanced.
Click to expand...

One method:

Use the *array* formula:

=INDEX(A1:BZ1,1,MATCH(TRUE,ISNUMBER(A1:BZ1),0))

To enter an array formula, after typing or pasting it into the cell, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.


--ron
 
Ron said:
One method:

Use the *array* formula:

=INDEX(A1:BZ1,1,MATCH(TRUE,ISNUMBER(A1:BZ1),0))

To enter an array formula, after typing or pasting it into the cell,
hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...}
around the
formula.


--ron
Click to expand...



Thanks it works well.

I personally try to avoid using array formula. Is there a different
approach that does not require the use of array formula?

Thanks again.
 
You could try the lookup formula, but with a very small number, eg
1E-200.

Hope this helps.

Pete
 
Thanks it works well.

I personally try to avoid using array formula. Is there a different
approach that does not require the use of array formula?

Thanks again.
Click to expand...

Possibly.

You could always write a UDF in VBA.
--ron
 
Morrigan said:
I personally try to avoid using array formula. Is there a different
approach that does not require the use of array formula?
Click to expand...

=INDEX(A1:BZ1,MATCH(1,INDEX(ISNUMBER(A1:BZ1)+0,0),0))
 
Back
Top