data sorting problem

WATKINS · Apr 25th, 2002, 05:42 AM

I have a large list of file reference numbers in the form 12/34/56789, which I need to sort by the last 2 digits only. Unfortunately each number is confined to a single cell. Rather than transfer by hand to a separate cell is there any way to isolate and sort the numbers I'm interested in? A further complication is that initial zeros are omitted: ie 12/34/(000)89, so the numbers are of varying length.

philip

Tom Schreiner · Apr 25th, 2002, 05:46 AM

Hi
Place this formula in an un-used column:

For example:

Your "12/34/56789" numbers are in column A
beginning at row 2 and going to row 2000

Place this in an unused column in row 2:

=Right(A2,2)

Copy the formula down to row 2000
Now sort using the new column as the key.

Tom

[ This Message was edited by: TsTom on 2002-04-25 04:46 ]

WATKINS · Apr 25th, 2002, 09:05 AM

fast response Tom, thanks. I knew about delimiting but that wouldn't have done the whole job here. you've saved me and my co-workers quite a bit of time.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 25th, 2002, 05:42 AM	#1
WATKINS New Member Join Date: Apr 2002 Posts: 2	I have a large list of file reference numbers in the form 12/34/56789, which I need to sort by the last 2 digits only. Unfortunately each number is confined to a single cell. Rather than transfer by hand to a separate cell is there any way to isolate and sort the numbers I'm interested in? A further complication is that initial zeros are omitted: ie 12/34/(000)89, so the numbers are of varying length. philip

Apr 25th, 2002, 05:46 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Place this formula in an un-used column: For example: Your "12/34/56789" numbers are in column A beginning at row 2 and going to row 2000 Place this in an unused column in row 2: =Right(A2,2) Copy the formula down to row 2000 Now sort using the new column as the key. Tom [ This Message was edited by: TsTom on 2002-04-25 04:46 ]

Apr 25th, 2002, 09:05 AM	#3
WATKINS New Member Join Date: Apr 2002 Posts: 2	fast response Tom, thanks. I knew about delimiting but that wouldn't have done the whole job here. you've saved me and my co-workers quite a bit of time.