Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by number only in cell
I have files with a bunch of cells with mixed number/text information. I
would like to sort by the number only. Each cell contains a 1-3 digit number, and 0-2 letters in some combination of prefix and suffix. For example, a list might be: 3 A100C A100D AB205 B4 53DE And I would like it sorted by the number only. Actually, I would like it sorted by the number, then the suffix, then the prefix, but the number is a big step in that direction... Thank you :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by number only in cell
On Wed, 15 Jul 2009 15:46:01 -0700, serendipity258
wrote: I have files with a bunch of cells with mixed number/text information. I would like to sort by the number only. Each cell contains a 1-3 digit number, and 0-2 letters in some combination of prefix and suffix. For example, a list might be: 3 A100C A100D AB205 B4 53DE And I would like it sorted by the number only. Actually, I would like it sorted by the number, then the suffix, then the prefix, but the number is a big step in that direction... Thank you :) Set up three blank columns to one side of your data. Label your columns: Data Number Prefix Suffix Assuming your Data starts in A2, enter the following formulas to extract the parts of the data: Number: =LOOKUP(9.9E+307,--MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&"1,2,3,4, 5,6,7,8,9,0")),ROW(INDIRECT("1:"&LEN(A2))))) Prefix: =LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&"1,2,3, 4,5,6,7,8,9,0"))-1) Suffix: =MID(A2,LEN(B2)+LEN(C2)+1,255) Then select the formulas and fill down as far as your data extends. Finally, with a selection in the table range, or after selecting the table: Data/Sort First by Number Then by Suffix Then by Prefix --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by number only in cell
Microsoft has a ready-rolled array formula:
http://office.microsoft.com/en-gb/ex...549011033.aspx Don't forget Ctrl-Shift-Enter ?B?c2VyZW5kaXBpdHkyNTg=?= wrote in : 3 A100C A100D AB205 B4 53DE |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number sort in Excel | Excel Discussion (Misc queries) | |||
Sort by part of a number | New Users to Excel | |||
Sort by number not by letter (C1, A2, B3, D4) | Excel Discussion (Misc queries) | |||
sort by section number | Excel Discussion (Misc queries) | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions |