Format Cell and Extract Lowest Value from the Cell

C

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!
 
J

Jason Morin

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

HTH
Jason
Atlanta, GA
 
G

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
 
P

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.
 
Top