Format Cell and Extract Lowest Value from the Cell

[Craig]

Hi,

I would like to format cell A1 as follows:

###/###

Sample Data in cell A1:
123/456
020/555
686/136

It must have the /

Then I would like to be able to extract from cell A1 the
lower of the two values between the /, in the first
example, 123, and place the result in cell B1.

Final Results:

A1 B1 = lowest value between the / in A1
123/456 123
020/555 020
686/136 136

Thanks!

 

[Jason Morin]

=MIN(LEFT(TEXT(A1,"###/###"),FIND("/",TEXT(A1,"###/###"))-
1)*1,MID(TEXT(A1,"###/###"),FIND("/",TEXT(A1,"###/###"))
+1,255))

HTH
Jason
Atlanta, GA

 

[Guest]

Sorry, I meant to say that the data is in column A and the
results are in column B.

A1 B1
123/456 123
A2 B2
020/555 020
A3 B3
686/136 136

 

[Pete McCosh]

Craig,
if you just type your entries like that it'll display them
the way you want, but formatted as text. If this is fine,
the following formula will do what you want:

=IF(LEFT(A1,3)<RIGHT(A1,3),LEFT(A1,3),RIGHT(A1,3))

If you want to force it to treat them as numbers, then
=IF(INT(LEFT(A6,3))<INT(RIGHT(A6,3)),INT(LEFT(A6,3)),INT
(RIGHT(A6,3)))

will do it, but you'll lose the three digit formatting of
the result (Number Format > Custom > 000 to re-establish)
and it'll cause an error if you introduce any text.

Pete.

 

[Craig]

Fantastic!

Thank you Pete and Jasen!

Craig