Forcing Excel to Sort Cells as Text

As long as your selection is a single range, you don't need a loop:

Sub Maketext2()
Selection.NumberFormat = "@"
Selection = Selection.Value
End Sub

Hello!

I have had similar issues entering different number series into Excel (not formatted as text), and discovered a problem. When entering (as an example) “61-10” Excel converts the data to “oct-61”. If I then copy/past that data into a text formatted cell, it gives the result “22555”.

The easiest way seems to be to remember to start with a text formatted sheet:)

(Swedish version of Excel)

@Alan
Thanks for coming back to me.
I found the answer on this, which you have now touched on.
It didn't matter if I copied or entered the values into text formatted column; whatever I did, clicking Sort did it wrong... until...

The answer was to do what you've said... go to Custom Sort. Excel then asks (very poorly IMO) how you want to sort the values.
By selecting "Sort numbers ad numbers stored as text separately", it sorts the column the way we want it to.

But more than that. Once this is done, this remains the default for future sorting (for text formatted columns). I tried again this morning with a new workbook and entered a column of values and simply clicked the Sort button. It did it according to the selection I made yesterday!
But yes... must format the column as Text first.

Sorted!

Thanks
Mike

@Mike - if you made the column text and MANUALLY entered in the numbers, you should be able to sort it the way you want it. If you "copied" in the numbers all bets are off.

1) check the figures are indeed "all" text with =ISTEXT(CellRef).
If you want to convert them to text there are lots of function that will do it, I usually just use =TRIM(CellRef). You can also convert it using Text to Columns and requesting the output to be text.

2) If they are definitely are all text then when you get the sort options, choose the 2nd one
"Sort numbers and numbers stored as text separately"

Hi

Excel Pro 2010

Even when I open a new sheet and format the column as text and then enter the following list...
755008
755006
755013
755010D
755002
755006D
755010

The sort result is...
755002
755006
755008
755010
755013
755006D
755010D

I need...
755002
755006
755006D
755008
755010
755010D
755013

Google sheets does it correctly.
How can we get Excel to do it right?

I even tried your paste values method but does not work.

Regards
Mike

Many years ago I worked for an aerospace company and we used parts with nunmbers like, GTS123-23, B1234, BA9876 and 7010234, and 77021234. These naturally sort with numbers first so I assumed that Kris needed the Text Values to sort first and then the numbers. My solution would be to use a helper with a lower case 'z' prefix and sort on that. You could use a macro to create the prefix, something like:

Sub AddPrefix()
Dim c As Range
For Each c In Selection
If IsNumeric(c) Then
c.Value = "z" & c
End If
Next
End Sub

To Louis:

It rather looks like Excel recognizes the "e" as such and the accented "e" variations as an ever so slightly different "e" and sorts them after the "e", but does NOT differentiate between them. (Differentiates between a plain vowel and its accented versions, so the plain vowel sorts first, but then the accented versions remain in the order they first had as they don't sort between each other.) To put it in terms of the CODE() function, it is as if Excel sees the "e" as 101 and ALL the accented variations as 101.5.

Could be a good suggestion to make at Excel.UserVoice.com.

To Alex:

I cannot make your report happen if the column I begin with is formatted as text. I get the desired 1, 10, 101, 1a, 1b, 2, 20. Unless... when Excel asks about sorting anything that looks like a number as a number, if I choose that option, I get your results, but if I choose the other option, I get the desired result.

There seems to be a way of doing this without having to change the format of either the part number or helper columns (leave both as General). If part numbers are in column A and helper column is B:

B1 = TEXT(A1,"#") etc.

Select column B, then Custom Sort. On the Sort Warning dialog, select 'Expand the selection'.

On the Sort dialog, Column Sort by = B, Sort on = Values, Order = A to Z. Press <OK>.

On the Sort Warning dialog, select Sort numbers and numbers stored as text separately.

I use "Text to Columns" (Data tab/Excel 2010) to the same thing.

Louis, this is going a bit off topic but I expect to get the result you want you need to change your Excel Language options. Under Excel > Options > Choose Editing Language it says
"Add additional languages to edit your documents. The editing languages set language-specific features, including dictionaries, grammar checking, and sorting"

Many languages use accentuated characters. I expect a sort result where the order would respect the left to right order. A short (french) example shows this is not the case. The first column shows the actual result, the second column shows the order I expect.
Benne Benne
Bénne Benne/tonneau
Bènne Bénne
Benne/tonneau Bènne
Beton Beton
Béton Béton
Bétonnière Bétonnière
How can this result be obtained. It certainly is of international intrest.

I did not find that the proposed solution worked.
I tested it on Excel 2010 and 2016 and step 5 (Use Paste Special to paste only the values to the new column), will not make the numbers text. Although the values all left align and appear to be text an istext() formula will prove that they are not.

This of course means that when you sort the column, the numbers they will still sort as numbers.
(My sample sorted as 1, 2, 10, 20, 101, 1a, 1b etc)

My proposed solution was to apply a text to column conversion on column A (the part number), applying any delimiter that is not used ie TAB and select the output format as Text. You will then still need to format the whole column as Text since only the converted data will have applied the Text formatting.

Manual data entry will apply the columns Text characteristic and Paste Value of numbers that are already text will carry across as text, but Paste Value of a number not already text will still come across as a number in the new Text column.

The MACRO also doesn’t work, you need to modify the code to something like this c.Value = Trim(c.Value).
As it stands it also only makes it appear to be text since it left aligns but istext() and a sort will show that it is not.