Selecting Lowest Number from Data in a Single Cell Separated by Comma

Hi All,

I am trying to figure out a way I can select the lowest number from data in a single cell separated by commas. And I want it to automatically select the lowest value when I edit the data.

For Example:


Cell A1 has 2,3,4,NA. I want it to select the lowest value which is 2 and the value should come in Cell B1.
Cell A2 has only NA. and I want it to automatically select it in Cell B2.

Could you please help me with this as I am not too great at excel and have been trying to figure this out since a few days.

Cheers,
Hasan

Delimited data in a single cell is a bad habit to get into.

Try this array formula. If the formula returns the first number from A1 instead of the lowest then it would mean that the array has not been confirmed correctly.

=IF(A1="NA",A1,MIN(IFERROR(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(A$1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*LEN(A1)+1,LEN(A1))+0,"")))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Thanks alot Jason! Worked like a charm! :D