A question (sort of)

[Joe C]

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

 

[Tim879]

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))

 

[David Biddulph]

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)

 

[Bernie Deitrick]

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

 

[Dave Peterson]

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.