After importing data to numbers columns not sorting properly

In your sample document, create a new column to the right (column E), select the entire column and change the format of the cells to Automatic (they started out as Text when I did it). Put the formula in cell E1. Select cell E1 and Copy (cmd C), select the entire column, and Paste (cmd V) to fill the entire column with the formula. After that, select the entire column again, Copy, select column D, then use Paste Formula Results from the Edit menu to overwrite the existing text with your new text. Finally, you can delete column E.

(Paste Values, which is what I called it in my earlier post, is the Excel name for Paste Formula Results. Forget I said it)

Caps are the same as not when sorting so I am thinking something else must be going on. Offhand I cannot think of a reason for imported text to become capitalized when it was not before, so it is already strange. It might be helpful if you make a new document, copy some of the data to it (and ensure it has the same problem in the new document), then post that document somewhere for us to examine (Dropbox, share on iCloud, etc). Or you might add a temporary new column and try a formula like =CODE(A), where column A has the addresses that are not sorting correctly. This will give you the ASCII code of the first character of the strings, which is the main character it is sorting on. Compare two addresses that should be sorted next to each other and see if it provides any clues.

Where did this document originate? Was it on the web, was it a document you created in Excel, Windows or Mac version of Excel, is it really a CSV (comma separated value) file or tab separated file,, etc.? Problems are more likely with files from the web, they may not be encoded strictly to the Excel spec Numbers is expecting.

I received your file. The problematic city names all have a leading space. Space characters are lower in value than alphanumerics and will sort to the top in an ascending sort.

First thing: back up your file in case something messes up. After that, create a new column and in that column use the formula =TRIM(D) to get rid of all extraneous spaces in the city names (column D being the column of city names). Copy the entire column then Paste Values into column D, overwriting the city names that were there. After that you can get rid of the new column.

The addresses in column C start with numbers and the numbers seem to be sorting correctly. They sort like text when they are part of text or in cells formatted as text. So you will get a sort order like 1, 10, 101, 11, 2, 3,...

If your actual table has street names without the street numbers and it is not sorting properly, I suspect it, too, has leading spaces that should be removed. Use the same method as given above for the city names.

If you don't have a place where you can post it, you can email it to me at badunit@cox.net