Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Not Working on Column of Numbers
Below is the outcome of my sort when the cells were formatted as number
These are currency values. I copied and pasted the information from a website into Excel and all cells were formatted as "General" when I checked my spreadsheet. I have tried to change the cell formatting to everything imaginable, currency, numbers, removed commas, cleared $ sign formats etc yet it appears that Excel is treating these numbers as text. The help section said to change the cell format to number from text. Yet the data was formatted as "general". It seems to be sorting from the left rather then by total value. 99198 9500 94012 92661 9175 9172 88933 86999 86250 8620 86154 8400 82335 81678 795888 78689 765552 Thanks for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Not Working on Column of Numbers
The format is affecting how the data is shown, not how it's stored or
evaluated. To check whether a column is truly numeric, I usually just highlight it and click on the 'comma style' button. If it's numeric, you'll see the comma delimiter and probably a decimal point with a couple digitis following. (After checking, click 'undo'). To convert text to numbers, click in a blank cell. Ctrl-C to copy. Highlight your column to be converted. Right-click, select Paste Special, select Values and click OK. Now your text should be coverted to numeric values and the sort should work as you're expecting. "therube" wrote: Below is the outcome of my sort when the cells were formatted as number These are currency values. I copied and pasted the information from a website into Excel and all cells were formatted as "General" when I checked my spreadsheet. I have tried to change the cell formatting to everything imaginable, currency, numbers, removed commas, cleared $ sign formats etc yet it appears that Excel is treating these numbers as text. The help section said to change the cell format to number from text. Yet the data was formatted as "general". It seems to be sorting from the left rather then by total value. 99198 9500 94012 92661 9175 9172 88933 86999 86250 8620 86154 8400 82335 81678 795888 78689 765552 Thanks for your help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Not Working on Column of Numbers
Hi,
To convert the entries to figures, highlight the range and go to Data Text tot columns and then click on Finish. This will convert all the entries to numbers. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "therube" wrote in message ... Below is the outcome of my sort when the cells were formatted as number These are currency values. I copied and pasted the information from a website into Excel and all cells were formatted as "General" when I checked my spreadsheet. I have tried to change the cell formatting to everything imaginable, currency, numbers, removed commas, cleared $ sign formats etc yet it appears that Excel is treating these numbers as text. The help section said to change the cell format to number from text. Yet the data was formatted as "general". It seems to be sorting from the left rather then by total value. 99198 9500 94012 92661 9175 9172 88933 86999 86250 8620 86154 8400 82335 81678 795888 78689 765552 Thanks for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Not Working on Column of Numbers
HI,
The numbers are really text as everyone is telling you. If you are using 2002-3 then there should be a green triangle at the top right of the cells. Highlight the range and put your mouse over the green error checking triangle and not to popup icon to the left, put your mouse over it and click it to see a list of options, choose Convert to Number. Unfortunately, Paste Special Values will not do it. Here the secret is to select an empty cell and copy it, then select all the text numbers and choose Edit, Paste Special, Add. This command is ~100 times faster than the Convert to numbers command. So if you have a large amount of data if would be the better of the two. Also, the first option is not available in earlier versions of Excel but this one has always been around. The Text to Columns command is also a good choice. -- If this helps, please click the Yes button Cheers, Shane Devenshire "bapeltzer" wrote: The format is affecting how the data is shown, not how it's stored or evaluated. To check whether a column is truly numeric, I usually just highlight it and click on the 'comma style' button. If it's numeric, you'll see the comma delimiter and probably a decimal point with a couple digitis following. (After checking, click 'undo'). To convert text to numbers, click in a blank cell. Ctrl-C to copy. Highlight your column to be converted. Right-click, select Paste Special, select Values and click OK. Now your text should be coverted to numeric values and the sort should work as you're expecting. "therube" wrote: Below is the outcome of my sort when the cells were formatted as number These are currency values. I copied and pasted the information from a website into Excel and all cells were formatted as "General" when I checked my spreadsheet. I have tried to change the cell formatting to everything imaginable, currency, numbers, removed commas, cleared $ sign formats etc yet it appears that Excel is treating these numbers as text. The help section said to change the cell format to number from text. Yet the data was formatted as "general". It seems to be sorting from the left rather then by total value. 99198 9500 94012 92661 9175 9172 88933 86999 86250 8620 86154 8400 82335 81678 795888 78689 765552 Thanks for your help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Not Working on Column of Numbers
Thanks Shane; you're correct, I meant to suggest Paste Special Add. Paste
Special Values was wrong. "Shane Devenshire" wrote: HI, The numbers are really text as everyone is telling you. If you are using 2002-3 then there should be a green triangle at the top right of the cells. Highlight the range and put your mouse over the green error checking triangle and not to popup icon to the left, put your mouse over it and click it to see a list of options, choose Convert to Number. Unfortunately, Paste Special Values will not do it. Here the secret is to select an empty cell and copy it, then select all the text numbers and choose Edit, Paste Special, Add. This command is ~100 times faster than the Convert to numbers command. So if you have a large amount of data if would be the better of the two. Also, the first option is not available in earlier versions of Excel but this one has always been around. The Text to Columns command is also a good choice. -- If this helps, please click the Yes button Cheers, Shane Devenshire "bapeltzer" wrote: The format is affecting how the data is shown, not how it's stored or evaluated. To check whether a column is truly numeric, I usually just highlight it and click on the 'comma style' button. If it's numeric, you'll see the comma delimiter and probably a decimal point with a couple digitis following. (After checking, click 'undo'). To convert text to numbers, click in a blank cell. Ctrl-C to copy. Highlight your column to be converted. Right-click, select Paste Special, select Values and click OK. Now your text should be coverted to numeric values and the sort should work as you're expecting. "therube" wrote: Below is the outcome of my sort when the cells were formatted as number These are currency values. I copied and pasted the information from a website into Excel and all cells were formatted as "General" when I checked my spreadsheet. I have tried to change the cell formatting to everything imaginable, currency, numbers, removed commas, cleared $ sign formats etc yet it appears that Excel is treating these numbers as text. The help section said to change the cell format to number from text. Yet the data was formatted as "general". It seems to be sorting from the left rather then by total value. 99198 9500 94012 92661 9175 9172 88933 86999 86250 8620 86154 8400 82335 81678 795888 78689 765552 Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i sort on a perticular column for odd and even numbers | Excel Worksheet Functions | |||
how do I sort a column mixed with odd and even numbers .. ? | Excel Discussion (Misc queries) | |||
HOW DO I SORT A COLUMN OF NUMBERS AND HAVE THE DUPLICATES REMOVED | Excel Discussion (Misc queries) | |||
sort and return column numbers | Excel Worksheet Functions | |||
sort a column of numbers with 100510 and 10160 so they are in th. | Excel Discussion (Misc queries) |