How do I format a cell for a custom part number?

[Guest]

Using Excel 2003;

I am trying to format a range of cells to accept hardware dimensions so I
can sort them by product code, diameter, pitch, and length. Since I can only
sort 3 columns, it would be helpful to combine pitch and diameter in a custom
number format.
Ideally I want to be able to enter diameter and pitch in the same cell as
follows:
"(diameter in whole numbers or fractions)-(pitch in whole numbers)"

For example, here are some common sizes:
10-24
10-32
1/4-20
5/16-18

I know how to get to the custom number format, but I don't understand how to
use the number codes to create the custom number format I need.

 

[Dave O]

Hi, PJ-
Before you get too wrapped up in a workaround, may I offer a slightly
different solution? Your note says "Since I can only sort 3 columns"
which leads me to believe you'd prefer to be able to sort on more
columns than that. You can, if you insert a new column (which can
later be hidden from view) that concatenates the values of several
cells.

For instance: you have data laid out in columns A thru G, and you want
to be able to sort on every column. You can do this by adding a new
column I: on each row the formula in I is
=A1&B1&C1&D1&E1&F1&G1
.... which takes the entry from each cell and jams it together into one
value. You can then sort on column I, which is effectively the same as
sorting on each of the seven columns. You can then hide this column if
necessary, or leave it off your printed output.

Does this provide any increase in data precision? Or alleviate the
need to format something so it's real pretty?

Dave O

 

[Walt Weber]

Hi PJ,

If you truly want a sort by diameter included, you should
probably convert all diameters to decimal values (Perhaps
another inserted column with lookups against a conversion
table). Otherwise, the screw samples you listed (10
guage, 1/4", and 5/16") will sort with 10 guage listing as
bigger than a quarter of an inch when it isn't.

Best Regards,
Walt

 

[Dave Peterson]

You can only sort on 3 columns at a time, but you can do multiple sorts.

Sort in ascending order in groups of 3 fields at a time. Excel will remember
the previous sort and not damage those results.

 

[Guest]

Thanks Dave, I believe that is what I am looking for. I'm trying to clean up
orders by classifying the parts ordered, and avoid doing multiple sorts.