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

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

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

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

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