Number Format

[Mike]

Hello All,
Using Excel XP.

In column A I will be entering data that has a format of the first two
numbers are years (eg: 06) followed by a dash, then a number from 1 to
9999, another dash and then ending with the letters PR. The data will look
like this:

A1: 06-45-PR
A2: 06-123-PR
A3: 04-68-PR
A4: 06-6-PR

After putting the data in column A I want to sort them by ascending order by
#, so that in the above example I want to get:
04-68-PR
06-6-PR
06-45-PR
06-123-PR .

But .it sorts by the first two digits and then the 1st digit after the first
dash:
04-68-PR
06-123-PR
06-45-PR
06-6-PR

I've tried making a number format of 00-0000-PR but can't get it to come out
right.
Any help would be appreciated.

Mike

 

[Dave Peterson]

If you only type in the numbers, you could use a custom number format of:
00-0000-P\R

 

[Mike]

Hi Dave & Group,
I'm sorry I forgot to mention that the numbers after the year should show up
without the zeros (but sorted that way). I tried the number format
00-0000-P\R, if I type in 6123, i get 00-6123-PR, not 06-123-PR.
Thanks,
Mike

 

[Dave Peterson]

I think you're going to have to make a choice--looks or sorting.

If it's ok to use a couple of helper columns, you could use those helper columns
for input and sorting, but hide them (or exclude them from the print area) and
only show the formatting the way you like:

Say columns A and B are the input fields:
=text(a2,"00")&"-"&b2&"-PR"

Then you can use columns A:B for sorting.

 

[Mike]

Thanks Dave,
I'll use that last suggestion you gave with the helper columns.

Mike

 

[Ron Rosenfeld]

Hello All,
Using Excel XP.

In column A I will be entering data that has a format of the first two
numbers are years (eg: 06) followed by a dash, then a number from 1 to
9999, another dash and then ending with the letters PR. The data will look
like this:

A1: 06-45-PR
A2: 06-123-PR
A3: 04-68-PR
A4: 06-6-PR

After putting the data in column A I want to sort them by ascending order by
#, so that in the above example I want to get:
04-68-PR
06-6-PR
06-45-PR
06-123-PR .

But .it sorts by the first two digits and then the 1st digit after the first
dash:
04-68-PR
06-123-PR
06-45-PR
06-6-PR

I've tried making a number format of 00-0000-PR but can't get it to come out
right.
Any help would be appreciated.

Mike

You could extract the numeric portion to form a number; put this into a helper
column; and then sort on the helper column.

A formula that would extract the equivalent number from your string is:

=LEFT(A1,2)*10^3+TEXT(MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1),"000")

Then just sort on this helper column (and hide it).

The formula assumes that your earliest year would be 2000. If you might have
years in the last century, we could alter the formula.


--ron