Sort error (Excel 2002)

I have a customer who is receiving the following error message in Excel 2002 when he tries to sort a large amount of data (almost 1500 records)
"The following sort key may not sort as expected because it contains some numbers formated as text. What would you like to do?
- Sort anything that looks like a number as a number
- Sort numbers and numbers stored as text separately"
The numbers are actually ISBN numbers (customer is a book seller), and they have been imported from a file received from a vendor. They really are a mix of numbers and text, and even if we format the entire column as text, we still receive this error message. The ISBN number starts with a leading zero - that's why the column needs to be formated as text. Unfortunately, the column was not formatted as text when the data was imported and the original file is no longer available to import again. Any suggestions? I can't find any information on the Microsoft site.

Re: Sort error (Excel 2002)

Lets say those ISBN numbers are in column A. Then you can convert them all to text this way:

1- Insert a new empty column between A and B, it will now be column B.

2- Select columns A and B and format both columns as text.

3- In B1 enter the formula:

<pre>=IF(ISTEXT(A1),A1,TEXT(A1,"000000000000000000 0"))
</pre>

In the above formula, replace the zeros between the double quotes with the number of digits you want in the ISBN numbers. If you want ten digits in the ISBN numbers, then put ten zeros between the quotes.

4- Select B1 and double click in the fill box in the lower right corner. This should fill the formula down and you should now have all of the values displayed as text.

5- Select column B and then click on Copy in the Edit menu.

6- Select A1 and then click on "Paste Special" in the Edit menu.

7- In the dialog box, in the Paste section, click on the radio button next to Values. Then click on OK. Column A should now be replaced with text values.