Remember Me?

#1
December 21st 05, 05:21 PM posted to microsoft.public.excel.misc
 CS Project Man Posts: n/a
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
December 21st 05, 05:56 PM posted to microsoft.public.excel.misc
 FinRazel Posts: n/a
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
December 21st 05, 05:57 PM posted to microsoft.public.excel.misc
 B. R.Ramachandran Posts: n/a
Sorting Alpha Numeric

Hi,

(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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post JACOB Excel Worksheet Functions 14 June 16th 05 04:10 PM samriepe Excel Discussion (Misc queries) 1 June 9th 05 03:17 PM David Excel Worksheet Functions 4 June 8th 05 05:19 PM worldmade Excel Discussion (Misc queries) 2 May 26th 05 03:44 PM 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.