Previous Topic Next Topic

Who to identify least one in excel

Closed
kathapr Posts 1 Registration date Thursday July 17, 2014 Status Member Last seen July 17, 2014 - Jul 17, 2014 at 10:58 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Jul 17, 2014 at 11:38 AM
Hi,

I have 10 Cells can be filled with between the text "L1" to "L20". In that we want find what is the least LXX text is . Someone can we help me out. How to do that in excel 2010

Thanks in advance

2 replies

Answer 1 / 2
Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 146
Jul 17, 2014 at 11:31 AM
kathapr, Good morning.

Suppose you have the range E2:E10 filled with theses texts.

Try to use it:

ARRAY FORMULA
G2 --> =SMALL(VALUE(RIGHT(E2:E10,LEN(E2:E10)-1)),1)

Don't type enter at the end of formula.
Use CTRL + SHIFT + ENTER
If things are ok will appears { and } at the formula.

Is it what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Jul 17, 2014 at 11:38 AM
Hi Kathapr,

You can use the following formula to extract the number from your string:
=SUM(RIGHT(A1,LEN(A1)-1)+0)
NOTE that I use SUM(value+0) to convert the cell property to number (couldn't do it manually for some reason).

Once you extracted your numbers you can use the MIN function to determine the lowest value.

You can also nest the first formula's in the second to use only one cell. Like:
=MIN(SUM(RIGHT(A1,LEN(A1)-1)+0),SUM(RIGHT(A2,LEN(A2)-1)+0),SUM(RIGHT(A3,LEN(A3)-1)+0))

Best regards,
Trowa
0