Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 21st 05, 05:21 PM posted to microsoft.public.excel.misc
CS Project Man
 
Posts: n/a
Default Sorting Alpha Numeric

I have a list of alpha numerics I wish to sort but they are coming up as the
Excel 'default'. I prefer the desired result. Any thoughts? Thanks

Original Sorted Desired
D10 D07 D07
D09 D09 D09
D07 D10 D10
D11 D105 D11
D12 D11 D12
D20 D117 D20
D117 D12 D105
D105 D20 D117

  #2   Report Post  
Old December 21st 05, 05:56 PM posted to microsoft.public.excel.misc
FinRazel
 
Posts: n/a
Default Sorting Alpha Numeric

your data isn't sorting properly because it is type text, which always sorts
alphabetically left-right (so it won't distinguish between a 1 in the tens
or hundreds place). I have two suggestions:

1. If you don't have much data, re-enter all of it with a zero in the
hundreds place for all numbers below 100 (D07 becomes D007)

2. If you have a lot of data, copy your column into a new column.
Highlight this column. Using Edit-Replace, Replace the "D" with nothing.
Then, Format- Cells - Number - Number. This will change the data in this
column to number format. Sort using this column.
--
Anne Murray


"CS Project Man" wrote:

I have a list of alpha numerics I wish to sort but they are coming up as the
Excel 'default'. I prefer the desired result. Any thoughts? Thanks

Original Sorted Desired
D10 D07 D07
D09 D09 D09
D07 D10 D10
D11 D105 D11
D12 D11 D12
D20 D117 D20
D117 D12 D105
D105 D20 D117

  #3   Report Post  
Old December 21st 05, 05:57 PM posted to microsoft.public.excel.misc
B. R.Ramachandran
 
Posts: n/a
Default Sorting Alpha Numeric

Hi,

Do all the alpha-numeric strings in your worksheet start with the letter "D"
(as in your sample data). If that is the case, a quick solution is:

Let's suppose that the strings are in A2:A101. Enter the following array
formula in B2.
="D"&SMALL(MID($A$2:$A$101,2,255)*1,ROW(A1))
Confirm with CTRL-SHIFT-ENTER, and auto-fill the formula down to B101.

Regards,
B. R. Ramachandran

"CS Project Man" wrote:

I have a list of alpha numerics I wish to sort but they are coming up as the
Excel 'default'. I prefer the desired result. Any thoughts? Thanks

Original Sorted Desired
D10 D07 D07
D09 D09 D09
D07 D10 D10
D11 D105 D11
D12 D11 D12
D20 D117 D20
D117 D12 D105
D105 D20 D117



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
VLOOKUP WITH ALPHA NUMERIC JACOB Excel Worksheet Functions 14 June 16th 05 04:10 PM
Columns are now numeric, not alpha. how to change back? samriepe Excel Discussion (Misc queries) 1 June 9th 05 03:17 PM
Search string for alpha or numeric David Excel Worksheet Functions 4 June 8th 05 05:19 PM
The colums changed from alpha to numeric how do you make it alpha worldmade Excel Discussion (Misc queries) 2 May 26th 05 03:44 PM
Alpha & Numeric Counts in Excel Programmer wanna be Excel Discussion (Misc queries) 3 April 5th 05 11:12 AM


All times are GMT +1. The time now is 03:13 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017