Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A question (sort of)
Hello,
I can't seem to get these things to sort. They are 1/2 2/12 2/2 6/2 3/12 1/1 These things are formatted as text and do sort as expected 2/12 2/2 But I need them to be sorted as if there were a 0 preceding the 2 in the 2/2. Any ideas? Papi |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A question (sort of)
there's probably a smarter way to do this, but you could use the
formula below to reformat the data and then sort the reformatted data as you describe above. =IF(LEN(LEFT(A1,FIND("/",A1)-1))=1,REPT("0", 1)&LEFT(A1,FIND("/",A1)-1),LEFT(A1,FIND("/",A1)-1))&"/"&IF(LEN(MID(A1,FIND("/",A1)+1,100))=1,REPT("0", 1)&MID(A1,FIND("/",A1)+1,100),MID(A1,FIND("/",A1)+1,100)) On Jan 24, 8:13 am, Joe C wrote: Hello, I can't seem to get these things to sort. They are 1/2 2/12 2/2 6/2 3/12 1/1 These things are formatted as text and do sort as expected 2/12 2/2 But I need them to be sorted as if there were a 0 preceding the 2 in the 2/2. Any ideas? Papi |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
A question (sort of)
Well, you could have a helper column with ="0"&A2, and sort by that, but
with a zero in front of everything they'll sort in the same order as you've already got. If, however, you inteded the zero to be in front of the SECOND 2 in 2/2, and not in front of the 12 in 2/12, then perhaps something like =LEFT(A2,FIND("/",A2))&IF(LEN(A2)-FIND("/",A2)=2,"","0")&RIGHT(A2,LEN(A2)-FIND("/",A2)) But is the number before the slash always a single digit, or if not should the single digit ones have a zero before them too? If, of course, those are supposed to represent dates and they are in your system default format (whether m/d or d/m), then the easy option is for your helper column to be =DATEVALUE(A2) -- David Biddulph "Joe C" wrote in message ... Hello, I can't seem to get these things to sort. They are 1/2 2/12 2/2 6/2 3/12 1/1 These things are formatted as text and do sort as expected 2/12 2/2 But I need them to be sorted as if there were a 0 preceding the 2 in the 2/2. Any ideas? Papi |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
A question (sort of)
Joe,
Either use two helper columns of formulas (and then sort based on those two columns) =VALUE(LEFT(A2,FIND("/",A2)-1)) =VALUE(SUBSTITUTE(A2,LEFT(A2,FIND("/",A2)),"")) OR Format the cells for date (m/d), then enter 1 into a blank cell, copy that cell, select your cells with the 1/2 etc, choose paste special, and select Value and Multiply. That will convert those values into dates that will sort correctly. HTH, Bernie MS Excel MVP "Joe C" wrote in message ... Hello, I can't seem to get these things to sort. They are 1/2 2/12 2/2 6/2 3/12 1/1 These things are formatted as text and do sort as expected 2/12 2/2 But I need them to be sorted as if there were a 0 preceding the 2 in the 2/2. Any ideas? Papi |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
A question (sort of)
One more option.
You could insert two columns to the right of this column and use Data|Text to columns, delimited by the slash and format each receiving field as general. Then you could sort your data based on these two helper columns. Joe C wrote: Hello, I can't seem to get these things to sort. They are 1/2 2/12 2/2 6/2 3/12 1/1 These things are formatted as text and do sort as expected 2/12 2/2 But I need them to be sorted as if there were a 0 preceding the 2 in the 2/2. Any ideas? Papi -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
question on VB-sort | Excel Discussion (Misc queries) | |||
Sort question?? | Excel Discussion (Misc queries) | |||
sort question | Excel Worksheet Functions | |||
Sort question | Excel Discussion (Misc queries) | |||
sort question | Excel Discussion (Misc queries) |