Hey,

I have a column of cells that contain either 1,2,3 or 4 numbers listed as so:

1
1,3
1,3,8
4,5,6,7

What I want is for a cell to look at the numbers and give me the 2 largest
numbers. If there is only 1 number, then it can return just that one number.


Thanks.
matt

The easiest way to do this is in two steps:

1. Separate your data into cells by using Text to Columns
2. Get the two largest numbers using the Large function.

Post back if you need more help on either. Also tell us what version of
Excel you are using.

Regards,
Fred.

"mpenkala" wrote in message
...
Hey,

I have a column of cells that contain either 1,2,3 or 4 numbers listed as
so:

1
1,3
1,3,8
4,5,6,7

What I want is for a cell to look at the numbers and give me the 2 largest
numbers. If there is only 1 number, then it can return just that one
number.


Thanks.
matt

Hi Fred,
spliting the data with text to columns doesn't work because the numbers are
deterined using a formula. So when I go to use text to columns, it attempts
to break up the formula instead of the numbers given.

Any other ideas? Thanks,
Matt


"Fred Smith" wrote:

The easiest way to do this is in two steps:

1. Separate your data into cells by using Text to Columns
2. Get the two largest numbers using the Large function.

Post back if you need more help on either. Also tell us what version of
Excel you are using.

Regards,
Fred.

"mpenkala" wrote in message
...
Hey,

I have a column of cells that contain either 1,2,3 or 4 numbers listed as
so:

1
1,3
1,3,8
4,5,6,7

What I want is for a cell to look at the numbers and give me the 2 largest
numbers. If there is only 1 number, then it can return just that one
number.


Thanks.
matt

On Wed, 16 Apr 2008 15:41:02 -0700, mpenkala
wrote:

Hey,

I have a column of cells that contain either 1,2,3 or 4 numbers listed as so:

1
1,3
1,3,8
4,5,6,7

What I want is for a cell to look at the numbers and give me the 2 largest
numbers. If there is only 1 number, then it can return just that one number.


Thanks.
matt

Are the numbers always sorted ascending? Then:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))<2,A1,
MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,",",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,",",""))-1)),99))

--ron

Assume your numbers are sort in ascending order\
then try this:

=RIGHT(A1,3)
copy down


"mpenkala" wrote:

Hey,

I have a column of cells that contain either 1,2,3 or 4 numbers listed as so:

1
1,3
1,3,8
4,5,6,7

What I want is for a cell to look at the numbers and give me the 2 largest
numbers. If there is only 1 number, then it can return just that one number.


Thanks.
matt

In which case, you can split your formula to put the answers into separate
cells.
--
David Biddulph

"mpenkala" wrote in message
...
Hi Fred,
spliting the data with text to columns doesn't work because the numbers
are
deterined using a formula. So when I go to use text to columns, it
attempts
to break up the formula instead of the numbers given.

Any other ideas? Thanks,
Matt


"Fred Smith" wrote:

The easiest way to do this is in two steps:

1. Separate your data into cells by using Text to Columns
2. Get the two largest numbers using the Large function.

Post back if you need more help on either. Also tell us what version of
Excel you are using.

Regards,
Fred.

"mpenkala" wrote in message
...
Hey,

I have a column of cells that contain either 1,2,3 or 4 numbers listed
as
so:

1
1,3
1,3,8
4,5,6,7

What I want is for a cell to look at the numbers and give me the 2
largest
numbers. If there is only 1 number, then it can return just that one
number.


Thanks.
matt

Hey Ron - this one works great, thanks!

Matt

"Ron Rosenfeld" wrote:

On Wed, 16 Apr 2008 15:41:02 -0700, mpenkala
wrote:

Hey,

I have a column of cells that contain either 1,2,3 or 4 numbers listed as so:

1
1,3
1,3,8
4,5,6,7

What I want is for a cell to look at the numbers and give me the 2 largest
numbers. If there is only 1 number, then it can return just that one number.


Thanks.
matt

Are the numbers always sorted ascending? Then:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))<2,A1,
MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,",",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,",",""))-1)),99))

--ron

Hey mama,

this would work great except for some of my numbers are double digits.

Thanks though.
Matt


"Teethless mama" wrote:

Assume your numbers are sort in ascending order\
then try this:

=RIGHT(A1,3)
copy down


"mpenkala" wrote:

Hey,

I have a column of cells that contain either 1,2,3 or 4 numbers listed as so:

1
1,3
1,3,8
4,5,6,7

What I want is for a cell to look at the numbers and give me the 2 largest
numbers. If there is only 1 number, then it can return just that one number.


Thanks.
matt

On Thu, 17 Apr 2008 05:41:01 -0700, mpenkala
wrote:

Hey Ron - this one works great, thanks!

Matt

Glad to help. Thanks for the feedback.
--ron