Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Ontario, Canada
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

    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"))

    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.

    8- Delete column B.

    You should now be able to sort on column A and get what you expect.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts