Excel Sorting Issue - Error on sort for complex numbers saved as text

Question

Excel Sorting Issue - Error on sort for complex numbers saved as text

EORiggs 0

I have a table with a listing of Standard Occupation Code (SOC) values. These numbers contain two digits followed by a hyphen, followed by 4 digits. Example: 99-9099.

My issue is that a single cell value (11-1021) will not sort properly. All other values appear correctly ordered, but 11-1021 remains at the end of the codes starting in 11-.

Can someone please explain why this happens and provide a solution?

0 comments

2 answers

Your answer

Answer 1

Hi @EORiggs,

Since all SOC codes follow the same XX-XXXX format, Excel should be able to sort them correctly even when they are stored as text. If only 11-1021 appears at the end of the 11- group, there might be something different about how that particular value is stored.

I'd suggest trying the steps suggested by the forum's AI-generated response:

Clear the affected cell completely.
Change its number format to Text.
Manually type 11-1021 again using a standard hyphen.
Sort the entire table by the SOC column (A to Z).

You can also check for hidden characters using the formula =LEN(A2) (assuming A2 is the affected cell). The expected result for 11-1021 is 7. If it returns a different number, there may be extra spaces or hidden characters in the cell.

Could you give these steps a try and see whether you notice anything different? Please post any updates or results as a reply to my post below so I can receive a notification. The forum's AI can't follow up on your progress, but I'll be happy to help investigate further based on what you find.

Thank you so much for your time.

EORiggs 0 Reputation points

2026-11-13T15:18:05.2966667+00:00

Thanks. I had already tried these steps before posting. No hidden CHARs. Just this element is the issue. I'll try to recreate the entire worksheet manually adding in codes and see if the issue is resolved.

Answer 2

Delete the contents of the affected cell, format it as Text, and type 11-1021 again manually; a hidden leading character, nonbreaking space, or nonstandard hyphen in that one value is the most likely reason it sorts separately.

Excel can display two strings identically while sorting them differently because imported or pasted text may contain spaces or nonprinting characters. Microsoft specifically notes that these characters can produce unexpected sort results.^1^

Fix the single cell

Select the cell containing the problematic 11-1021.
Press Delete to clear it completely—do not merely edit the existing value.
On Home → Number Format, choose Text.
Type 11-1021 using the keyboard, including a normal hyphen (-).
Sort the entire table again by the SOC column, A to Z.

SOC values are identifiers rather than quantities, so keeping the whole column as Text is appropriate.

Check whether the cell contains hidden characters

Assuming the problematic value is in A2, enter these formulas in empty cells:

=LEN(A2)
=CODE(LEFT(A2,1))
=CODE(RIGHT(A2,1))

A correctly entered 11-1021 has seven visible characters, so LEN(A2) should return 7. A larger result indicates an extra character. CODE can expose an unexpected first or last character, although it does not identify every Unicode character.

Clean multiple affected values

If other codes were imported from a website, PDF, CSV, or external system, insert a helper column and use:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Then:

Fill the formula down.
Confirm the cleaned codes look correct.
Copy the helper column.
Use Paste Special → Values over the original SOC column.
Format that column as Text and sort again.

TRIM removes ordinary extra spaces, CLEAN removes ASCII nonprinting characters, and SUBSTITUTE handles character 160, the common nonbreaking space. CLEAN alone does not remove every Unicode nonprinting character.^2^

If the formula still leaves the value misplaced, the displayed hyphen may be a Unicode dash rather than the standard keyboard hyphen. For this single known code, clearing and manually retyping it is safer than applying a broad replacement to the workbook.

References