Left justifying numbers.

[crdennis]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)

Our office machine has item numbers for parts that very in length. They are left justified in their sorts.
Example:
188632, 1886325, 188655, 18889700, 190271, 191060, 194210

If I sort another column and then later try to resort it by item number, it wants to sort these numerically. I've tried to say they are text, but that doesn't work either. Is there a way to sort "left justified"?

 

[CyberTaz]

The trick is that the cells need to be formatted as Text *before* putting
the data into them... And I'm afraid that pasting (even Paste Special -
Values) won't change things. There may be a shorter way than having to
re-enter the content in to properly formatted cells, but I don't know what
to suggest. Perhaps someone else can offer an alternative.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

The trick is that the cells need to be formatted as Text *before* putting
the data into them... And I'm afraid that pasting (even Paste Special -
Values) won't change things. There may be a shorter way than having to
re-enter the content in to properly formatted cells, but I don't know what
to suggest. Perhaps someone else can offer an alternative.

HTH |:>)
Bob Jones
[MVP] Office:Mac

Sure. To convert the numbers back to text, in a new column, type the formula
=A1&" ". Fill this down for as long as you need. Then copy the data from
the new column, and paste-special-values back into the original cells. Now
all the numbers will be text. You could alternatively use the formula:
="'"&a1 to prefix all the numbers with an apostrophe, forcing the value to
be text. The apostrophe will not show in the cell.