Removing numbers to the left

[mpenkala]

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

 

[Fred Smith]

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]

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

 

[Ron Rosenfeld]

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

 

[Teethless mama]

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

=RIGHT(A1,3)
copy down

 

[mpenkala]

Hey Ron - this one works great, 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

 

[mpenkala]

Hey mama,

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

Thanks though.
Matt

 

[Ron Rosenfeld]

Hey Ron - this one works great, thanks!

Matt

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