Excel expert?

  • 1 Replies

Ezzino Milonga

  • *
  • Newbie
  • *
  • Posts: 49
  • Ball don't lie!!!
« on: August 30, 2019, 02:51:44 PM »
Hi guys..i wish to ask about an issue I have in my lists, created using the google sheets.
I noticed that, when I try to order some column in which there are words and numbers, it don't works properly.
For example, if I try to order the column to check in what position of the world standings the courses are, the number 100 is after the number 1..the number 8 is between the 80 and the 79..ans so go on...ans the effect is stupid to see.

So..someone knows what is the option to use to have a correct order for words and numbers together?
"Competitive golf is played mainly on a five-and-a-half-inch course... the space between your ears" Bobby Jones


  • *
  • Newbie
  • *
  • Posts: 44
« Reply #1 on: August 30, 2019, 10:49:26 PM »
Your alpha-numeric data is sorted as if it were only alphabetical or all characters (no distinguishing between numbers and letters).  So, Google Sheets sorts on the first character of each value.  When two or more values have the same first character, Google Sheets looks at the second character to decide the sort order, and so on.  You can't do anything (that I'm aware of) to change this sorting method - you have to change the data if you want it sorted differently.

In this case, changing the data would be a very simple task but possibly very tedious.  If the numerical values are three digits or less (that is, 999 and below), you would have to make ALL of the numerical values three characters long.  So 1 becomes 001, 79 becomes 079, 80 becomes 080, and 100 remains as 100 since the it already is 3 characters long.  If you only have rankings up to 99, then you'd only have to change all the data to 2 characters for it to sort 'properly'.

It might be simpler for you to 'extract' the numerical ranking to a different column than the text (course name?) and then use the filters/sorting of the resulting table to get what you want.

Best of luck with your sheet!