find least non-zero number from a range incl. text and numbers

[Goyal]

Requirement: identify cell value starting with number excluding 0 from a range of cells containing text or blanks.

I could write a query when all cells contain numeric value.

INDEX(A1:C1,0,MATCH(MIN(IF(VALUE(LEFT(A1:C1))<>0,VALUE(LEFT(A1:C1)))),VALUE(LEFT(A1:C1)),0))

But this doesn't work when cells include text or blanks. in the below example, I am expecting "1-Not Started" as the output ...

Many thx for your support.

 

[Peter_SSs]

Welcome to the MrExcel board!

identify cell value starting with number excluding 0 ..... I am expecting "1-Not Started" as the output ..

Why are you expecting "1-Not Started" as opposed to "3- In Progress" or "0-N/A" ?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[Peter_SSs]

Why are you expecting "1-Not Started" as opposed to "3- In Progress" or "0-N/A" ?

On re-reading, I think that I have answered my own questions. Try this (assuming that you have the LET function).

21 06 17.xlsm
	A	B	C	D	E	F	G
1	0-N/A	cm	3-In Progress	prime	1- Not Started		1- Not Started
Detect Number

Cell Formulas
Range	Formula
G1	G1	=LET(fltr,FILTER(A1:E1,(ISNUMBER(LEFT(A1:E1,1)+0))*(LEFT(A1:E1,1)<>"0")),INDEX(SORTBY(fltr,LEFT(fltr,FIND("-",fltr&"-")-1)+0),1))

 

[Rick Rothstein]

I think this array-entered** formula will also work...

=MIN(IF((LEFT(A1:E1)<>"0")*ISNUMBER(-LEFT(A1:E1)),0+LEFT(A1:E1,FIND("-",A1:E1&"-")-1),FALSE))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Peter_SSs]

I think this array-entered** formula will also work...

=MIN(IF((LEFT(A1:E1)<>"0")*ISNUMBER(-LEFT(A1:E1)),0+LEFT(A1:E1,FIND("-",A1:E1&"-")-1),FALSE))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Hi Rick
The OP has 365 so no need for C+S+E entry. However, the formula only returns that leading number (1 in this case), not ...

I am expecting "1-Not Started" as the output

.. but could perhaps be modified to

=INDEX(A1:E1,MATCH(MIN(IF((LEFT(A1:E1)<>"0")*ISNUMBER(-LEFT(A1:E1)),0+LEFT(A1:E1,FIND("-",A1:E1&"-")-1),FALSE))&"-*",A1:E1,0))

 

[Goyal]

many thx Peter, it worked like a charm

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.