How to sort number with text in large function?

  • Thread starter Thread starter Guest
  • Start date Start date
Hi Clara

Are you wanting to find the largest number based upon the numeric part of
the cell entry? If so, I think you will need to create a helper column first.

This formula is an adaptation of a solution posted by Harlan Grove on
02/10/2005 in .worksheet functions.

With your data in A1 enter in B1
=IF(A1="","",IF(LEFT(A1)>"9",--RIGHT(A1,LOOKUP(E2+300,-RIGHT(A1,ROW($1:$50)),ROW($1:$50))),
--LEFT(A1,LOOKUP(E2+300,-LEFT(A1,ROW($1:$50)),ROW($1:$50)))))
and copy down.

This will extract the numeric part of cells where there are leading or
trailing text values. Use the MAX() or LARGE()function on this column and
use INDEX() and MATCH() to return the original value (if required).

=INDEX(A:A,MATCH(LARGE(B:B,1),B:B))


Regards

Roger Govier
 
Hi Clara

My apologies, slight typo, not E2+300 but 2E+300 (i.e. a very large number)

=IF(A1="","",IF(LEFT(A1)>"9",--RIGHT(A1,LOOKUP(2E+300,-RIGHT(A1,ROW($1:$50)),ROW($1:$50))),

--LEFT(A1,LOOKUP(2E+300,-LEFT(A1,ROW($1:$50)),ROW($1:$50)))))

Regards

Roger Govier
 
Back
Top