ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Sorting Alpha Numeric (https://www.excelbanter.com/excel-discussion-misc-queries/61411-sorting-alpha-numeric.html)

CS Project Man

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

FinRazel

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


B. R.Ramachandran

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



All times are GMT +1. The time now is 02:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com