Extract largest or smallest value from a CELL

Hi all,

I have data such as the following:

54 x 0.5 = 2.5
0.5*2.898=78
Test:0.8*0.98=5.2
~0.85 (0.32*2.45)

And need to extract the smallest number in one row so,

0.5
0.5
0.8
0.32

And extract the largest number in another:

54
78
5.2
2.45

This needs to be done with EXCEL FUNCTIONS instead of macros as it will be distributed across other PCs. I found a formula that can extract 1st,2nd,3rd etc numbers in a cell, by changing C1 to 1,2,3 etc:
=LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--("0"&MID(
SUBSTITUTE(" "&A1," ","|"),ROW($1:$25),1)))=0)*ISNUMBER(--(MID(
SUBSTITUTE(" "&A1," ","|"),ROW($2:$26),1)))),ROW($2:$26)),C1),ROW($1:$25)))

Tried to modify it to check smaller or larger number with on luck, maybe could help someone?

Any help would be greatly appreciated!

Just to be clear, the data is a string of text, such that your example would be in 4 rows and 1 column in the system, right?

Let's assume that's in column A.
If you already have something that can extract the numbers, and there's always three numbers in each row, then I'd use five more columns for each row like this:
B = 1st number
C = 2nd number
D = 3rd number
E = MIN(B1:D1) / outputs the smallest of the numbers in the row B1 to D1
F = MAX(B1:D1) / outputs the largest of the numbers in the row B1 to D1

That would be my general approach. If you want something more specific you would get more success posting an example spreadsheet for us to play with.

Apologies, yes the data is in 4 rows and 1 column.
That would work, didn't think of that! However, it would be useful if it could be combined to a single formula/cell, is that possible?

Yeah, just nest the formulas:
E = MIN( 1st_number_finder, 2nd_number_finder, 3rd_number_finder)
Like that.

...I say that, but it is an extremely rare usage where the costs of a bigger spreadsheet are greater than the costs of something that takes people longer to understand. If it was me I would push back against nesting them into a big ol' monster, and break them out so the numbers are each in their own column. If you have a screw up it would make it easier to find & debug, and if you need to get the SUM or AVE it's a trivial addition....

But it's your spreadsheet.

Try the attached file

Mr XOR LX, is there any wrong in the formula?

Good advice Ben, it's good to know it can be done both ways.
Thanks nflsales, that works as well! Have to aggree with XOR, longest formula I've ever seen haha

Formula:

Please Login or Register  to view this content.