Prev: In Excel, changing a range of cells to all caps.
Next: how do I sort by picked-up date in Excel
From: Anita on 1 Apr 2010 10:39
I have a list of various numbers and text that I want to sort numerically. Here is an example:
T300
D9745
P50
565
007
0245
052

Problems: 1.Getting leading zeros to show AND be recognized.
2. Sorting--when I try to sort, it will sort like 7,52, 245, 565, P50, T300, D9745. I want it to sort numerically first (007,0245,052,565) then alphabetically (D9745,P50, T300).
I'm assuming this is impossible, but any tips would be appreciated.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Break the Roles in SharePoint Lists
http://www.eggheadcafe.com/tutorials/aspnet/c3ac8915-3861-4406-bec7-42f2d9110d79/break-the-roles-in-sharep.aspx
From: Luke M on 1 Apr 2010 10:48
You are wanting to sort as if all values are text. First, create a helper
column, use formula:
=TEXT(A2,"@")
Then, select all your data you want sorted (helper column & original column)
and goto Data - Sort. Sort by helper column, treat numbers that look like
text as Text.

--
Best Regards,

Luke M
<Anita Marlay> wrote in message news:201041103950amarlay(a)lakeregional.com...
>I have a list of various numbers and text that I want to sort numerically.
>Here is an example:
> T300
> D9745
> P50
> 565
> 007
> 0245
> 052
>
> Problems: 1.Getting leading zeros to show AND be recognized.
> 2. Sorting--when I try to sort, it will sort like 7,52, 245, 565,
> P50, T300, D9745. I want it to sort numerically first (007,0245,052,565)
> then alphabetically (D9745,P50, T300).
> I'm assuming this is impossible, but any tips would be appreciated.
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> Break the Roles in SharePoint Lists
> http://www.eggheadcafe.com/tutorials/aspnet/c3ac8915-3861-4406-bec7-42f2d9110d79/break-the-roles-in-sharep.aspx


From: Gord Dibben on 1 Apr 2010 11:28
Precede the numbers with an apostrophe like

'007 '0245 '052

Data>Sort

"Sort anything that looks like a number, as a number"


Gord Dibben MS Excel MVP

On Thu, 01 Apr 2010 07:39:50 -0700, Anita Marlay wrote:

>I have a list of various numbers and text that I want to sort numerically. Here is an example:
>T300
>D9745
>P50
>565
>007
>0245
>052
>
>Problems: 1.Getting leading zeros to show AND be recognized.
> 2. Sorting--when I try to sort, it will sort like 7,52, 245, 565, P50, T300, D9745. I want it to sort numerically first (007,0245,052,565) then alphabetically (D9745,P50, T300).
>I'm assuming this is impossible, but any tips would be appreciated.
>
>
>Submitted via EggHeadCafe - Software Developer Portal of Choice
>Break the Roles in SharePoint Lists
>http://www.eggheadcafe.com/tutorials/aspnet/c3ac8915-3861-4406-bec7-42f2d9110d79/break-the-roles-in-sharep.aspx

 | 
Pages: 1
Prev: In Excel, changing a range of cells to all caps.
Next: how do I sort by picked-up date in Excel