Sorting on an address field, I get this:
940 MARKET ST
-940 N CAMPBELL
940 NORTH STATE ST
See the dash? ISTM that a dash is not the same as a nine, and so it appears to me to be out of order. How do I make Excel sort the file so that all addresses that have a leading dash are together so that I can get rid of the dashes?
How many dashes at the beginning are there? You could do a search for dash and replace it with nothing. You probably don’t want to do a “replace all” though, since there may be dashes in other locations.
Function =CODE() will return a numeric code of the first character in a cell.
Addresses starting with “-” should return 45, while address starting with a number or a letter should return values between 49 and 122.
I would suggest instead of sorting you filter with the filter begins with -
That will return only those with the leading - and you can fix them and resort.
Even better, create two new columns. Addresses are in column A.
Cell B1 (referring to address in cell A1) =IF(CODE(A1)=45, SUBSTITUTE(A1,"-","",1), A1)
Fill in column B. Then select the entire column B, copy and paste special (values only) into column C. Double-check the result and you can erase columns A and B.
I think the answer is you can’t make it work, if you need the hyphen. You could use a different character, however.
From here:
Assuming you are using the hyphen to try and force things to sort in a certain order (as opposed to it being an extraneous character, which a lot of the other suggestions seem to think).
Correct. Most of the dashes in that column are legitimate, so I don’t want to do a ‘replace all’. I could do a ‘find’ on the column and change them individually, but there are more than 25,000 records.
I have no idea what this means. Apparently you can do something to change the dashes to 45s? But then, how would I know the difference between a dash displayed as a 45, and a 45?
If I could sort the file so that all of the dashes are together, that would be ideal.
Grrr! :mad: Why Excel does so many things that are ‘stupid’ is unfathomable to me!
I went and changed all of the dashes to pipes, then sorted the file and deleted all of the (189) leading pipes at once, and then changed the remaining pipes back to dashes.
Thanks.
OK, I love the creative solution. But I like my answer better 
So, now that Johnny has fixed his data - any speculation on why Excel sort handle’s hyphens that way? I can mostly see ignoring the single quote - that’s how you tell Excel to treat something as a string (although it should be an option, I’d think).
But what reason is there to ignore hyphen’s as alpha-numeric characters?
Possibly because people who grew up in the 1950s or so often use a hyphen rather than a space in addresses: “123-Main Street” vs. “123 Main Street”, and you normally don’t want the sort to separate them.
Also, a leading hyphen is not valid (US postal system doesn’t allow negative house numbers). And Excel was designed in the USA.
Is it just single leading hyphens you are worried about? If that is the case, it is easy to fix without changing any data. All you have to do is create a formula in a blank column that gets rid of the leading hyphen and then sort on that derived column instead of the original one. If you are dealing with lots of different types of added characters, it can still be done but the formula to do it will get long and complicated really quickly.
You can use the technique AdamF describes using the Code() function except I would amend it to say that you don’t need to ever get rid of the orginal data. Just use a new column containing the function to do the sort. You can even hide the new column if you want to.
ETA - It sounds like you really want the leading hyphens to be used in the sort. The technique described above will still work for that but you have to get creative. Your formula will need to do something like change the hyphens to a string of zeros in a new column and then sort based on that so that those come up first. You may have to adjust what I am suggesting because I am having a hard time imagining exactly what you are trying to accomplish as and end result but you can build a special sort column that uses text manipulation built-in functions to do almost anything you want.
Some thoughts, since this has already been well-answered and the OP went off and solved it in a different tangent.
Using sorting as a way of grouping similar data to then make manual changes to it is already barking up the completely wrong tree.
Using the CODE() function is just obfuscation. A simple comparison of whether the first character is a “-” is equally effective and clearer, especially for users who are apparently novices at complex Excel formulas much less VBA.