Excel 2007 : List of Numbers - Sortable with partial numbers

[ms_guy99]

Good Day Everyone,
My worksheet has a column of site numbers. Most are three digit but some are five digit. When we identify a site it takes a while for its site number to be assigned, but based on the country it's located in we typically know either the first digit or the first two digits.

What I need is a format to enter the partially known numbers so that is still sort in numerical order correctly, including the partial numbers. If not a format then something!

Ex:
Site # Country Status
110 US Open
1## US Identified
126 US Open
103 US Closed
231 Canada Open
233 Canada Open
23? Canada Identified
245 UK Open
24? UK Identified
35467 Spain Open

I am using Excel 2007....just in case someone wanted to know.

Thanks In Advance!

[NBVC]

Perhaps try adding a helper column with formula:

=TEXT(A2,"@")

then sort by that column...

In the Sort Warning dialogue, choose to sort numbers and numbers stored as text separately.

[ms_guy99]

Ok...so that worked...but I was trying to keep from having an additional column due to the fact it would cause massive cofnusion with the other users. BUT......it never did occur to me that changeing the numbers to text would work....have started the process of making the whole column of numbers text (by adding ' )..and then in the future when others add sites I'll either be able to tell by the justification or wrong sorting wheter it is a number or text and will fix it then! So in essence....your solution was the solution!

THANKS!!!!!

[NBVC]

You can also convert the whole column to text by going to Data|Text to Columns.. skip to 3rd window and select Text from the column data format section... click Finish.