+ Reply to Thread
Results 1 to 19 of 19

Column sorting incorrectly

  1. #1
    Melissa
    Guest

    Column sorting incorrectly

    I have a column containing numbers 1-40. I made sure the format of these
    cells is "Number" then I sorted my spreadsheet in ascending order. The
    strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    after 40. Why is this happening?

  2. #2
    RWN
    Guest

    Re: Column sorting incorrectly

    Are you absolutely sure of the format? Sounds like the errant values are text.

    Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
    sort it will select the current region which is determined by where xl finds blank rows
    and columns.
    To be sure, select the values and then sort.

    HTH
    --
    Regards;
    Rob
    ------------------------------------------------------------------------
    "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    news:012D2404-9753-46AA-99E4-1640095B6476@microsoft.com...
    > I have a column containing numbers 1-40. I made sure the format of these
    > cells is "Number" then I sorted my spreadsheet in ascending order. The
    > strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    > after 40. Why is this happening?




  3. #3
    Melissa
    Guest

    Re: Column sorting incorrectly

    I expect that when I select my whole column and format cells to Number, that
    all the selected cells WILL have the format: Number.

    In response to your question on breaks, my spreadsheet does not contain any
    empty rows although somes cells may be blank.

    I selected all the relevant rows and columns before applying auto-filter.

    A bit more info on this column that I am sorting: this column contains
    numbers 1-40 plus some blank cells.

    "RWN" wrote:

    > Are you absolutely sure of the format? Sounds like the errant values are text.
    >
    > Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
    > sort it will select the current region which is determined by where xl finds blank rows
    > and columns.
    > To be sure, select the values and then sort.
    >
    > HTH
    > --
    > Regards;
    > Rob
    > ------------------------------------------------------------------------
    > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > news:012D2404-9753-46AA-99E4-1640095B6476@microsoft.com...
    > > I have a column containing numbers 1-40. I made sure the format of these
    > > cells is "Number" then I sorted my spreadsheet in ascending order. The
    > > strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    > > after 40. Why is this happening?

    >
    >
    >


  4. #4
    RWN
    Guest

    Re: Column sorting incorrectly

    > I expect that when I select my whole column and format cells to Number, that
    > all the selected cells WILL have the format: Number.


    Not necessarily. If a text value is already present then selecting the cell and changing
    the format won't have any effect (try it).
    To force the format to a number, select a blank cell and copy it. Then select the range
    where you require the format to be numeric and "PasteSpecial->Add".


    --
    Regards;
    Rob
    ------------------------------------------------------------------------
    "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    news:A6D47FB7-9866-44B4-B179-AF8E251F4FD0@microsoft.com...
    > I expect that when I select my whole column and format cells to Number, that
    > all the selected cells WILL have the format: Number.
    >
    > In response to your question on breaks, my spreadsheet does not contain any
    > empty rows although somes cells may be blank.
    >
    > I selected all the relevant rows and columns before applying auto-filter.
    >
    > A bit more info on this column that I am sorting: this column contains
    > numbers 1-40 plus some blank cells.
    >
    > "RWN" wrote:
    >
    > > Are you absolutely sure of the format? Sounds like the errant values are text.
    > >
    > > Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
    > > sort it will select the current region which is determined by where xl finds blank

    rows
    > > and columns.
    > > To be sure, select the values and then sort.
    > >
    > > HTH
    > > --
    > > Regards;
    > > Rob
    > > ------------------------------------------------------------------------
    > > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > > news:012D2404-9753-46AA-99E4-1640095B6476@microsoft.com...
    > > > I have a column containing numbers 1-40. I made sure the format of these
    > > > cells is "Number" then I sorted my spreadsheet in ascending order. The
    > > > strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    > > > after 40. Why is this happening?

    > >
    > >
    > >




  5. #5
    Max
    Guest

    Re: Column sorting incorrectly

    "Melissa" wrote:
    > .. I expect that when I select my whole column
    > and format cells to Number, that
    > all the selected cells WILL have the format: Number.


    Think formatting doesn't change the underlying ..

    Try this:

    Copy an empty cell, then select the range of 40 numbers
    Right-click > paste special > Check "Add" > OK

    Now the 40 numbers should sort properly
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



  6. #6
    Melissa
    Guest

    Re: Column sorting incorrectly

    I checked the format of the "errant" cells and it shows Number too.

    In future, how will I know that there's an "underlying" text format if I
    can't see it in the Format>Cell menu? It so happens I noticed the problem in
    this spreadsheet, which is also a small file.

    BTW, I did the pasteSpecial> Add and my column is sorting correctly now.
    Can you please explain what this "Add" operation does?

    Many thanks.

    "RWN" wrote:

    > > I expect that when I select my whole column and format cells to Number, that
    > > all the selected cells WILL have the format: Number.

    >
    > Not necessarily. If a text value is already present then selecting the cell and changing
    > the format won't have any effect (try it).
    > To force the format to a number, select a blank cell and copy it. Then select the range
    > where you require the format to be numeric and "PasteSpecial->Add".
    >
    >
    > --
    > Regards;
    > Rob
    > ------------------------------------------------------------------------
    > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > news:A6D47FB7-9866-44B4-B179-AF8E251F4FD0@microsoft.com...
    > > I expect that when I select my whole column and format cells to Number, that
    > > all the selected cells WILL have the format: Number.
    > >
    > > In response to your question on breaks, my spreadsheet does not contain any
    > > empty rows although somes cells may be blank.
    > >
    > > I selected all the relevant rows and columns before applying auto-filter.
    > >
    > > A bit more info on this column that I am sorting: this column contains
    > > numbers 1-40 plus some blank cells.
    > >
    > > "RWN" wrote:
    > >
    > > > Are you absolutely sure of the format? Sounds like the errant values are text.
    > > >
    > > > Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
    > > > sort it will select the current region which is determined by where xl finds blank

    > rows
    > > > and columns.
    > > > To be sure, select the values and then sort.
    > > >
    > > > HTH
    > > > --
    > > > Regards;
    > > > Rob
    > > > ------------------------------------------------------------------------
    > > > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > > > news:012D2404-9753-46AA-99E4-1640095B6476@microsoft.com...
    > > > > I have a column containing numbers 1-40. I made sure the format of these
    > > > > cells is "Number" then I sorted my spreadsheet in ascending order. The
    > > > > strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    > > > > after 40. Why is this happening?
    > > >
    > > >
    > > >

    >
    >
    >


  7. #7
    Melissa
    Guest

    Re: Column sorting incorrectly

    Oh, and why won't Format Painter work? I thought it's to apply the format to
    the new cells?

    "RWN" wrote:

    > > I expect that when I select my whole column and format cells to Number, that
    > > all the selected cells WILL have the format: Number.

    >
    > Not necessarily. If a text value is already present then selecting the cell and changing
    > the format won't have any effect (try it).
    > To force the format to a number, select a blank cell and copy it. Then select the range
    > where you require the format to be numeric and "PasteSpecial->Add".
    >
    >
    > --
    > Regards;
    > Rob
    > ------------------------------------------------------------------------
    > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > news:A6D47FB7-9866-44B4-B179-AF8E251F4FD0@microsoft.com...
    > > I expect that when I select my whole column and format cells to Number, that
    > > all the selected cells WILL have the format: Number.
    > >
    > > In response to your question on breaks, my spreadsheet does not contain any
    > > empty rows although somes cells may be blank.
    > >
    > > I selected all the relevant rows and columns before applying auto-filter.
    > >
    > > A bit more info on this column that I am sorting: this column contains
    > > numbers 1-40 plus some blank cells.
    > >
    > > "RWN" wrote:
    > >
    > > > Are you absolutely sure of the format? Sounds like the errant values are text.
    > > >
    > > > Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
    > > > sort it will select the current region which is determined by where xl finds blank

    > rows
    > > > and columns.
    > > > To be sure, select the values and then sort.
    > > >
    > > > HTH
    > > > --
    > > > Regards;
    > > > Rob
    > > > ------------------------------------------------------------------------
    > > > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > > > news:012D2404-9753-46AA-99E4-1640095B6476@microsoft.com...
    > > > > I have a column containing numbers 1-40. I made sure the format of these
    > > > > cells is "Number" then I sorted my spreadsheet in ascending order. The
    > > > > strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    > > > > after 40. Why is this happening?
    > > >
    > > >
    > > >

    >
    >
    >


  8. #8
    Stefi
    Guest

    Re: Column sorting incorrectly

    What is the source of these data? I experienced earlier that imported data
    (e.g. from a text file) behave like that!

    Stefi


    "Melissa" wrote:

    > I expect that when I select my whole column and format cells to Number, that
    > all the selected cells WILL have the format: Number.
    >
    > In response to your question on breaks, my spreadsheet does not contain any
    > empty rows although somes cells may be blank.
    >
    > I selected all the relevant rows and columns before applying auto-filter.
    >
    > A bit more info on this column that I am sorting: this column contains
    > numbers 1-40 plus some blank cells.
    >
    > "RWN" wrote:
    >
    > > Are you absolutely sure of the format? Sounds like the errant values are text.
    > >
    > > Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
    > > sort it will select the current region which is determined by where xl finds blank rows
    > > and columns.
    > > To be sure, select the values and then sort.
    > >
    > > HTH
    > > --
    > > Regards;
    > > Rob
    > > ------------------------------------------------------------------------
    > > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > > news:012D2404-9753-46AA-99E4-1640095B6476@microsoft.com...
    > > > I have a column containing numbers 1-40. I made sure the format of these
    > > > cells is "Number" then I sorted my spreadsheet in ascending order. The
    > > > strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    > > > after 40. Why is this happening?

    > >
    > >
    > >


  9. #9
    Melissa
    Guest

    Re: Column sorting incorrectly

    Yes, the values were imported. But the question is: why after formatting the
    cells to Number, are they still not behaving the way they should?

    "Stefi" wrote:

    > What is the source of these data? I experienced earlier that imported data
    > (e.g. from a text file) behave like that!
    >
    > Stefi
    >
    >
    > "Melissa" wrote:
    >
    > > I expect that when I select my whole column and format cells to Number, that
    > > all the selected cells WILL have the format: Number.
    > >
    > > In response to your question on breaks, my spreadsheet does not contain any
    > > empty rows although somes cells may be blank.
    > >
    > > I selected all the relevant rows and columns before applying auto-filter.
    > >
    > > A bit more info on this column that I am sorting: this column contains
    > > numbers 1-40 plus some blank cells.
    > >
    > > "RWN" wrote:
    > >
    > > > Are you absolutely sure of the format? Sounds like the errant values are text.
    > > >
    > > > Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
    > > > sort it will select the current region which is determined by where xl finds blank rows
    > > > and columns.
    > > > To be sure, select the values and then sort.
    > > >
    > > > HTH
    > > > --
    > > > Regards;
    > > > Rob
    > > > ------------------------------------------------------------------------
    > > > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > > > news:012D2404-9753-46AA-99E4-1640095B6476@microsoft.com...
    > > > > I have a column containing numbers 1-40. I made sure the format of these
    > > > > cells is "Number" then I sorted my spreadsheet in ascending order. The
    > > > > strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    > > > > after 40. Why is this happening?
    > > >
    > > >
    > > >


  10. #10
    Stefi
    Guest

    Re: Column sorting incorrectly

    In my opinion it's an Excel bug. I found a workaround: format a new helper
    column as number, copy your misbehaving column and paste special values in
    the helper column. Try to sort by it and if it is correct, you may copy it
    back in its original place if necessary.

    Regards,
    Stefi


    "Melissa" wrote:

    > Yes, the values were imported. But the question is: why after formatting the
    > cells to Number, are they still not behaving the way they should?
    >
    > "Stefi" wrote:
    >
    > > What is the source of these data? I experienced earlier that imported data
    > > (e.g. from a text file) behave like that!
    > >
    > > Stefi
    > >
    > >
    > > "Melissa" wrote:
    > >
    > > > I expect that when I select my whole column and format cells to Number, that
    > > > all the selected cells WILL have the format: Number.
    > > >
    > > > In response to your question on breaks, my spreadsheet does not contain any
    > > > empty rows although somes cells may be blank.
    > > >
    > > > I selected all the relevant rows and columns before applying auto-filter.
    > > >
    > > > A bit more info on this column that I am sorting: this column contains
    > > > numbers 1-40 plus some blank cells.
    > > >
    > > > "RWN" wrote:
    > > >
    > > > > Are you absolutely sure of the format? Sounds like the errant values are text.
    > > > >
    > > > > Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
    > > > > sort it will select the current region which is determined by where xl finds blank rows
    > > > > and columns.
    > > > > To be sure, select the values and then sort.
    > > > >
    > > > > HTH
    > > > > --
    > > > > Regards;
    > > > > Rob
    > > > > ------------------------------------------------------------------------
    > > > > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > > > > news:012D2404-9753-46AA-99E4-1640095B6476@microsoft.com...
    > > > > > I have a column containing numbers 1-40. I made sure the format of these
    > > > > > cells is "Number" then I sorted my spreadsheet in ascending order. The
    > > > > > strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    > > > > > after 40. Why is this happening?
    > > > >
    > > > >
    > > > >


  11. #11
    Melissa
    Guest

    Re: Column sorting incorrectly

    I posted this with the intention that somebody from Microsoft will confirm
    it's a bug. I think it's really silly... so am glad you agreed! :D

    "Stefi" wrote:

    > In my opinion it's an Excel bug. I found a workaround: format a new helper
    > column as number, copy your misbehaving column and paste special values in
    > the helper column. Try to sort by it and if it is correct, you may copy it
    > back in its original place if necessary.
    >
    > Regards,
    > Stefi
    >
    >
    > "Melissa" wrote:
    >
    > > Yes, the values were imported. But the question is: why after formatting the
    > > cells to Number, are they still not behaving the way they should?
    > >
    > > "Stefi" wrote:
    > >
    > > > What is the source of these data? I experienced earlier that imported data
    > > > (e.g. from a text file) behave like that!
    > > >
    > > > Stefi
    > > >
    > > >
    > > > "Melissa" wrote:
    > > >
    > > > > I expect that when I select my whole column and format cells to Number, that
    > > > > all the selected cells WILL have the format: Number.
    > > > >
    > > > > In response to your question on breaks, my spreadsheet does not contain any
    > > > > empty rows although somes cells may be blank.
    > > > >
    > > > > I selected all the relevant rows and columns before applying auto-filter.
    > > > >
    > > > > A bit more info on this column that I am sorting: this column contains
    > > > > numbers 1-40 plus some blank cells.
    > > > >
    > > > > "RWN" wrote:
    > > > >
    > > > > > Are you absolutely sure of the format? Sounds like the errant values are text.
    > > > > >
    > > > > > Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
    > > > > > sort it will select the current region which is determined by where xl finds blank rows
    > > > > > and columns.
    > > > > > To be sure, select the values and then sort.
    > > > > >
    > > > > > HTH
    > > > > > --
    > > > > > Regards;
    > > > > > Rob
    > > > > > ------------------------------------------------------------------------
    > > > > > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > > > > > news:012D2404-9753-46AA-99E4-1640095B6476@microsoft.com...
    > > > > > > I have a column containing numbers 1-40. I made sure the format of these
    > > > > > > cells is "Number" then I sorted my spreadsheet in ascending order. The
    > > > > > > strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    > > > > > > after 40. Why is this happening?
    > > > > >
    > > > > >
    > > > > >


  12. #12
    Stefi
    Guest

    Re: Column sorting incorrectly

    Melissa, Microsoft is a colossus on clay legs, it reacts very slowly. We have
    to survive day by day, therefore I'd rather like to know that could you come
    around the problem?
    Stefi


    "Melissa" wrote:

    > I posted this with the intention that somebody from Microsoft will confirm
    > it's a bug. I think it's really silly... so am glad you agreed! :D
    >
    > "Stefi" wrote:
    >
    > > In my opinion it's an Excel bug. I found a workaround: format a new helper
    > > column as number, copy your misbehaving column and paste special values in
    > > the helper column. Try to sort by it and if it is correct, you may copy it
    > > back in its original place if necessary.
    > >
    > > Regards,
    > > Stefi
    > >
    > >
    > > "Melissa" wrote:
    > >
    > > > Yes, the values were imported. But the question is: why after formatting the
    > > > cells to Number, are they still not behaving the way they should?
    > > >
    > > > "Stefi" wrote:
    > > >
    > > > > What is the source of these data? I experienced earlier that imported data
    > > > > (e.g. from a text file) behave like that!
    > > > >
    > > > > Stefi
    > > > >
    > > > >
    > > > > "Melissa" wrote:
    > > > >
    > > > > > I expect that when I select my whole column and format cells to Number, that
    > > > > > all the selected cells WILL have the format: Number.
    > > > > >
    > > > > > In response to your question on breaks, my spreadsheet does not contain any
    > > > > > empty rows although somes cells may be blank.
    > > > > >
    > > > > > I selected all the relevant rows and columns before applying auto-filter.
    > > > > >
    > > > > > A bit more info on this column that I am sorting: this column contains
    > > > > > numbers 1-40 plus some blank cells.
    > > > > >
    > > > > > "RWN" wrote:
    > > > > >
    > > > > > > Are you absolutely sure of the format? Sounds like the errant values are text.
    > > > > > >
    > > > > > > Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
    > > > > > > sort it will select the current region which is determined by where xl finds blank rows
    > > > > > > and columns.
    > > > > > > To be sure, select the values and then sort.
    > > > > > >
    > > > > > > HTH
    > > > > > > --
    > > > > > > Regards;
    > > > > > > Rob
    > > > > > > ------------------------------------------------------------------------
    > > > > > > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > > > > > > news:012D2404-9753-46AA-99E4-1640095B6476@microsoft.com...
    > > > > > > > I have a column containing numbers 1-40. I made sure the format of these
    > > > > > > > cells is "Number" then I sorted my spreadsheet in ascending order. The
    > > > > > > > strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    > > > > > > > after 40. Why is this happening?
    > > > > > >
    > > > > > >
    > > > > > >


  13. #13
    Melissa
    Guest

    Re: Column sorting incorrectly

    Yes, I had to manually update the format of the "errant" cells by:
    1. Copying a blank cell
    2. Selecting the errant cell, and
    3. Paste> Special> Add

    No idea why it works but it did (thanks to other replies to my post).

    "Stefi" wrote:

    > Melissa, Microsoft is a colossus on clay legs, it reacts very slowly. We have
    > to survive day by day, therefore I'd rather like to know that could you come
    > around the problem?
    > Stefi
    >
    >
    > "Melissa" wrote:
    >
    > > I posted this with the intention that somebody from Microsoft will confirm
    > > it's a bug. I think it's really silly... so am glad you agreed! :D
    > >
    > > "Stefi" wrote:
    > >
    > > > In my opinion it's an Excel bug. I found a workaround: format a new helper
    > > > column as number, copy your misbehaving column and paste special values in
    > > > the helper column. Try to sort by it and if it is correct, you may copy it
    > > > back in its original place if necessary.
    > > >
    > > > Regards,
    > > > Stefi
    > > >
    > > >
    > > > "Melissa" wrote:
    > > >
    > > > > Yes, the values were imported. But the question is: why after formatting the
    > > > > cells to Number, are they still not behaving the way they should?
    > > > >
    > > > > "Stefi" wrote:
    > > > >
    > > > > > What is the source of these data? I experienced earlier that imported data
    > > > > > (e.g. from a text file) behave like that!
    > > > > >
    > > > > > Stefi
    > > > > >
    > > > > >
    > > > > > "Melissa" wrote:
    > > > > >
    > > > > > > I expect that when I select my whole column and format cells to Number, that
    > > > > > > all the selected cells WILL have the format: Number.
    > > > > > >
    > > > > > > In response to your question on breaks, my spreadsheet does not contain any
    > > > > > > empty rows although somes cells may be blank.
    > > > > > >
    > > > > > > I selected all the relevant rows and columns before applying auto-filter.
    > > > > > >
    > > > > > > A bit more info on this column that I am sorting: this column contains
    > > > > > > numbers 1-40 plus some blank cells.
    > > > > > >
    > > > > > > "RWN" wrote:
    > > > > > >
    > > > > > > > Are you absolutely sure of the format? Sounds like the errant values are text.
    > > > > > > >
    > > > > > > > Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
    > > > > > > > sort it will select the current region which is determined by where xl finds blank rows
    > > > > > > > and columns.
    > > > > > > > To be sure, select the values and then sort.
    > > > > > > >
    > > > > > > > HTH
    > > > > > > > --
    > > > > > > > Regards;
    > > > > > > > Rob
    > > > > > > > ------------------------------------------------------------------------
    > > > > > > > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > > > > > > > news:012D2404-9753-46AA-99E4-1640095B6476@microsoft.com...
    > > > > > > > > I have a column containing numbers 1-40. I made sure the format of these
    > > > > > > > > cells is "Number" then I sorted my spreadsheet in ascending order. The
    > > > > > > > > strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    > > > > > > > > after 40. Why is this happening?
    > > > > > > >
    > > > > > > >
    > > > > > > >


  14. #14
    Stefi
    Guest

    Re: Column sorting incorrectly

    Thanks for your reply! If you want to fix a single cell, just select it,
    click on the formula bar and click the tick mark, this method also works on a
    single cell. Interesting, isn't it?
    Stefi


    "Melissa" wrote:

    > Yes, I had to manually update the format of the "errant" cells by:
    > 1. Copying a blank cell
    > 2. Selecting the errant cell, and
    > 3. Paste> Special> Add
    >
    > No idea why it works but it did (thanks to other replies to my post).
    >
    > "Stefi" wrote:
    >
    > > Melissa, Microsoft is a colossus on clay legs, it reacts very slowly. We have
    > > to survive day by day, therefore I'd rather like to know that could you come
    > > around the problem?
    > > Stefi
    > >
    > >
    > > "Melissa" wrote:
    > >
    > > > I posted this with the intention that somebody from Microsoft will confirm
    > > > it's a bug. I think it's really silly... so am glad you agreed! :D
    > > >
    > > > "Stefi" wrote:
    > > >
    > > > > In my opinion it's an Excel bug. I found a workaround: format a new helper
    > > > > column as number, copy your misbehaving column and paste special values in
    > > > > the helper column. Try to sort by it and if it is correct, you may copy it
    > > > > back in its original place if necessary.
    > > > >
    > > > > Regards,
    > > > > Stefi
    > > > >
    > > > >
    > > > > "Melissa" wrote:
    > > > >
    > > > > > Yes, the values were imported. But the question is: why after formatting the
    > > > > > cells to Number, are they still not behaving the way they should?
    > > > > >
    > > > > > "Stefi" wrote:
    > > > > >
    > > > > > > What is the source of these data? I experienced earlier that imported data
    > > > > > > (e.g. from a text file) behave like that!
    > > > > > >
    > > > > > > Stefi
    > > > > > >
    > > > > > >
    > > > > > > "Melissa" wrote:
    > > > > > >
    > > > > > > > I expect that when I select my whole column and format cells to Number, that
    > > > > > > > all the selected cells WILL have the format: Number.
    > > > > > > >
    > > > > > > > In response to your question on breaks, my spreadsheet does not contain any
    > > > > > > > empty rows although somes cells may be blank.
    > > > > > > >
    > > > > > > > I selected all the relevant rows and columns before applying auto-filter.
    > > > > > > >
    > > > > > > > A bit more info on this column that I am sorting: this column contains
    > > > > > > > numbers 1-40 plus some blank cells.
    > > > > > > >
    > > > > > > > "RWN" wrote:
    > > > > > > >
    > > > > > > > > Are you absolutely sure of the format? Sounds like the errant values are text.
    > > > > > > > >
    > > > > > > > > Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
    > > > > > > > > sort it will select the current region which is determined by where xl finds blank rows
    > > > > > > > > and columns.
    > > > > > > > > To be sure, select the values and then sort.
    > > > > > > > >
    > > > > > > > > HTH
    > > > > > > > > --
    > > > > > > > > Regards;
    > > > > > > > > Rob
    > > > > > > > > ------------------------------------------------------------------------
    > > > > > > > > "Melissa" <Melissa@discussions.microsoft.com> wrote in message
    > > > > > > > > news:012D2404-9753-46AA-99E4-1640095B6476@microsoft.com...
    > > > > > > > > > I have a column containing numbers 1-40. I made sure the format of these
    > > > > > > > > > cells is "Number" then I sorted my spreadsheet in ascending order. The
    > > > > > > > > > strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
    > > > > > > > > > after 40. Why is this happening?
    > > > > > > > >
    > > > > > > > >
    > > > > > > > >


  15. #15
    Ron Rosenfeld
    Guest

    Re: Column sorting incorrectly

    On Tue, 11 Oct 2005 23:15:03 -0700, "Melissa"
    <Melissa@discussions.microsoft.com> wrote:

    >Yes, the values were imported. But the question is: why after formatting the
    >cells to Number, are they still not behaving the way they should?


    You are confusing cell format with cell contents. They are really two
    different things.

    Changing the format of a cell does not change its contents.

    If you have already entered TEXT into a cell, changing the format does not
    change the nature of the Value that is stored.

    For many operations, Excel will give you the option of treating TEXT that looks
    like numbers, as numbers. You should get that option using the Data/Sort
    wizard (at least in versions 2002 and subsequent; I can't recall about before
    then).

    There are several ways to tell the difference. You can use the formula
    =ISTEXT(cell_ref); if you have not changed the default justification, TEXT
    will be left-justified and numbers will be right-justified.


    --ron

  16. #16
    Stefi
    Guest

    Re: Column sorting incorrectly

    Hi Ron,

    I am completely aware of the difference between cell format and cell
    content, but the problem is that in these cases data are NOT ENTERED via
    typing into the cells but are imported from a .txt file. I use Excel 2000 and
    have no experience with 2002 and above (I intend to test this phenomenon in
    Xl2003 in the near future), but in Xl2000 it definitely happens that sorting
    either numeric or text columns imported from a text file (all in General
    format) gives incorrect result. The same is the case with autofiltering.

    For some unkown reasons Xl2000 "senses" the real value of these cells
    contents only after manipulations written earlier in this thread. Note, that
    these operations don't deal with formatting (because formatting really
    doesn't fix the problem)!

    Regards,
    Stefi


    "Ron Rosenfeld" wrote:

    > On Tue, 11 Oct 2005 23:15:03 -0700, "Melissa"
    > <Melissa@discussions.microsoft.com> wrote:
    >
    > >Yes, the values were imported. But the question is: why after formatting the
    > >cells to Number, are they still not behaving the way they should?

    >
    > You are confusing cell format with cell contents. They are really two
    > different things.
    >
    > Changing the format of a cell does not change its contents.
    >
    > If you have already entered TEXT into a cell, changing the format does not
    > change the nature of the Value that is stored.
    >
    > For many operations, Excel will give you the option of treating TEXT that looks
    > like numbers, as numbers. You should get that option using the Data/Sort
    > wizard (at least in versions 2002 and subsequent; I can't recall about before
    > then).
    >
    > There are several ways to tell the difference. You can use the formula
    > =ISTEXT(cell_ref); if you have not changed the default justification, TEXT
    > will be left-justified and numbers will be right-justified.
    >
    >
    > --ron
    >


  17. #17
    Ron Rosenfeld
    Guest

    Re: Column sorting incorrectly

    On Wed, 12 Oct 2005 05:50:02 -0700, "Stefi" <Stefi@discussions.microsoft.com>
    wrote:

    >Hi Ron,
    >
    >I am completely aware of the difference between cell format and cell
    >content, but the problem is that in these cases data are NOT ENTERED via
    >typing into the cells but are imported from a .txt file. I use Excel 2000 and
    >have no experience with 2002 and above (I intend to test this phenomenon in
    >Xl2003 in the near future), but in Xl2000 it definitely happens that sorting
    >either numeric or text columns imported from a text file (all in General
    >format) gives incorrect result. The same is the case with autofiltering.
    >
    >For some unkown reasons Xl2000 "senses" the real value of these cells
    >contents only after manipulations written earlier in this thread. Note, that
    >these operations don't deal with formatting (because formatting really
    >doesn't fix the problem)!
    >
    >Regards,
    >Stefi


    Was there any implication that *you* were not aware of the difference between
    format and value?

    It is clear from the top of my message that my comments were directed towards a
    statement by Melissa.

    She was the poster who did not understand why changing the formatting did not
    help her problem.

    The reason XL2000 "senses the real value" is not "unknown". Rather it is
    because the "real value" is changed by the manipulations you outlined from
    being TEXT to being NUMERIC.


    --ron

  18. #18
    RWN
    Guest

    Re: Column sorting incorrectly

    I don't profess to be an expert but I *believe* the problem arises because the imported
    text data lacks a sign byte.
    Although it's a pain I don't think it is a "bug" (I run into the same problem uploading
    data to an I-Series).
    I've found that whenever I have to troubleshoot this sort of thing I enlarge the column
    width and kill any justification.
    The result is that the "text" numbers will be left justified and the "valid" numbers will
    be right justified.

    At work I use xl'03 and must say that I appreciate the warning I get from xl about
    detecting mixed formats-saves a lot of time when my "Vlookups" return #N/A's!

    --
    Regards;
    Rob
    ------------------------------------------------------------------------
    "Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
    news:j3fqk1dihrcamckt6ma7rdg88iqsi4085m@4ax.com...
    > On Wed, 12 Oct 2005 05:50:02 -0700, "Stefi" <Stefi@discussions.microsoft.com>
    > wrote:
    >
    > >Hi Ron,
    > >
    > >I am completely aware of the difference between cell format and cell
    > >content, but the problem is that in these cases data are NOT ENTERED via
    > >typing into the cells but are imported from a .txt file. I use Excel 2000 and
    > >have no experience with 2002 and above (I intend to test this phenomenon in
    > >Xl2003 in the near future), but in Xl2000 it definitely happens that sorting
    > >either numeric or text columns imported from a text file (all in General
    > >format) gives incorrect result. The same is the case with autofiltering.
    > >
    > >For some unkown reasons Xl2000 "senses" the real value of these cells
    > >contents only after manipulations written earlier in this thread. Note, that
    > >these operations don't deal with formatting (because formatting really
    > >doesn't fix the problem)!
    > >
    > >Regards,
    > >Stefi

    >
    > Was there any implication that *you* were not aware of the difference between
    > format and value?
    >
    > It is clear from the top of my message that my comments were directed towards a
    > statement by Melissa.
    >
    > She was the poster who did not understand why changing the formatting did not
    > help her problem.
    >
    > The reason XL2000 "senses the real value" is not "unknown". Rather it is
    > because the "real value" is changed by the manipulations you outlined from
    > being TEXT to being NUMERIC.
    >
    >
    > --ron




  19. #19
    Stefi
    Guest

    Re: Column sorting incorrectly

    Hi Ron,

    I didn't take upon myself your comment, although I really didn't notice that
    your comment is addressed to Melissa, sorry. I simply wanted to point out
    that before any formatting all cells format are "General" in a new sheet, let
    it be populated with either manually entered or imported data, and still
    there is a difference between the ways XL2000 handles them. Their format are
    the same (General), their value look identical, therefore it is misleading
    that you have to manipulate (not format) the imported ones in order to be
    handled in a normal way.

    To Rob! It's a matter of taste to consider this a bug or not.

    Regards,
    Stefi



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1