How to sort by ascending prices

[snefaerie]

I have a column with price data, and for some reason Calc thinks that in Ascending order, $10 comes before $2, etc. I thought maybe using the Format menu to change the Number category for that column to Currency would make it work, but it doesn’t.

Screenshot below shows the result when I do Sort → Sort Criteria → Sort Key 1 → “Price: TCGP” (column name), Ascending

Also, I thought of adding some leading zeroes in the Format → Numbers menu to see if that would resolve it, but the data comes from a linked external source and it doesn’t look like that data responds to formatting changes within the spreadsheet (at least as far as the Numbers submenu is concerned).

Any ideas on how to make it the Sort (Ascending) command work correctly here?

[anon73440385]

Hello

this happens because your prices are text and not numerical values. Convert them to numerical values and sorting will work as desired and expected (left alignment of the data, their sorting and the lack of an effect on numeric formatting is a clear indication for “text”-type of data).

How to convert text to numbers: See this FAQ

Hope that helps.

If the answer helped you to solve your problem, please click the check mark () next to the answer.

[mikekaganski]

Note also that normal Data->Sort includes Natural Sort option; the autofilter sorting doesn’t. You could file an enhancement request on adding that

[snefaerie]

When I check in the Format menu, though, it says that they are numbers. It also doesn’t seem to be responding to any changes I try to make in the Format menu because the data isn’t manually entered and is instead a link to external data. Is there no way to make this function work with external data links?

[anon73440385]

They are not numbers - you seem to have a misconception of what a format is.Formatting never changes the type of data stored. So you can format your text even as dates and the format dialog will show you dates, but the data keeps to be text. The the format dialog doesn’t show you the real nature of the data type. Formatting is not about changing data, but how the data is represented (shown to you). In fact a format never changes data but changes the way it presented to the user.

Just follow the instructions to change your text to numbers.

[snefaerie]

Got it, thank you!