sorting numbers and numbers that contain text in excel

[MZ]

A column contains both strictly numbers and also numbers that are followed
by text (e.g., row 1: 1000, row 2: 1500; row 3: 1000a; row 4: 1500a)
Identical numbers are related documents, with the text suffixes referring to
addenda documents; thus, document 1000 has an addendum document 1000a; How
can I sort the column so in the following order: row 1 (1000), row 3 (1000a),
row 2 (1500), row 4 (1500a)?
Thank you

 

[David Biddulph]

=TEXT(A1,"0") will turn each into text, then sort by that helper column (and
don't accept Excel's suggestion to treat text that looks like numbers as
numbers).

 

[MZ]

Thank you for the reply. I had already converted all the numbers into a text
format, yet it does not help.

 

[David Biddulph]

Are you sure that you converted the contents of the cell to text? How did
you do it?
Or did you merely change the format of the DISPLAY to text (which has no
effect on the cell contents)?
What does =ISTEXT(A2) say (& for other rows)?
If they really are all text but they don't sort correctly, perhaps you have
stray spaces or other non-printing characters? Does =LEN(A2) [and
correspondingly for other rows] show the length you expect for the text
string in the cell?