Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to sort number with text in large function?
How do I sort out a number with text (eg. INV897 and 103689a) in large
function key? |
#2
|
|||
|
|||
How to sort number with text in large function?
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 Clara wrote: How do I sort out a number with text (eg. INV897 and 103689a) in large function key? |
#3
|
|||
|
|||
How to sort number with text in large function?
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 Roger Govier wrote: 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 Clara wrote: How do I sort out a number with text (eg. INV897 and 103689a) in large function key? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA "Rnd" Function: Truly Random? | Excel Discussion (Misc queries) | |||
How do I sort a column a unique number? | Excel Worksheet Functions | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions | |||
Macro or Function to make text size to suite text Length? | Excel Discussion (Misc queries) | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) |