Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 18th 05, 10:36 AM
Clara
 
Posts: n/a
Default 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   Report Post  
Old October 18th 05, 11:21 AM
Roger Govier
 
Posts: n/a
Default 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   Report Post  
Old October 18th 05, 12:19 PM
Roger Govier
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA "Rnd" Function: Truly Random? TheRobsterUK Excel Discussion (Misc queries) 2 September 27th 05 04:50 AM
How do I sort a column a unique number? ChelleA Excel Worksheet Functions 7 February 19th 05 10:38 AM
Excel Sort function should not sort the cell formatting! Lisa D.N.1 Excel Worksheet Functions 1 December 28th 04 08:37 PM
Macro or Function to make text size to suite text Length? lbbss Excel Discussion (Misc queries) 4 December 14th 04 07:53 PM
Data > Sort function amnesia? Dave D Excel Discussion (Misc queries) 1 November 29th 04 10:44 PM


All times are GMT +1. The time now is 10:02 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017