Forcing Excel to Sort Cells as Text

Written by Allen Wyatt (last updated July 29, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


15

Kris works in an aerospace factory where they make gas turbine engine parts for many different companies. Each company has their own unique part numbers. Some are all numeric, but most are a combination of numbers and letters of varying lengths. When sorting by part number, Kris would like the column to be treated as text so the sorting comes out as he needs. He tried formatting the cells as text, but that doesn't always help. Also, he's not loving the idea of entering an apostrophe in each cell. Kris wonders if there is a simple way to make Excel sort all the part numbers as text.

Perhaps the most telling part of Kris' problem is the statement that he tried formatting the cells as text, but that doesn't always work. There is a trick to formatting the cells as text—you must do it before you enter information into the cells. If you try to format as text a cell containing a number, that doesn't change the number to text. However, if the cell is formatted as text before you enter the number, then the number is treated as text upon entry.

The solution is to re-enter all your numbers after you format your cells as text. This, of course, could be a major chore when you may be dealing with hundreds or thousands of part numbers. Instead, you could try the following general steps in which I assume the part numbers are in column A:

  1. Insert a column just to the right of your part numbers. (This new column is now column B.)
  2. Format column B as text.
  3. Select column A and press Ctrl+C. This copies the part numbers to the Clipboard.
  4. Select cell B1.
  5. Use Paste Special to paste only the values to the new column.

When you complete these steps, everything in column B should be treated as text and you can delete column A. You should now be able to sort by the part numbers and get the desired results.

There is a problem with this, of course. If you do a lot of copying and pasting of part numbers in the future, it is possible that all of the part numbers may no longer be treated as text. This occurs because if you use the traditional Ctrl+C and Ctrl+V to copy and paste, Excel pastes formatting into the cells as well. Thus, if the part number being pasted was previously formatted as a number, then it will be treated as a number after it is pasted.

In this case you could, if desired, use a macro to make sure your part numbers are always formatted as text. The following is just a short one that will work great:

Sub MakeText()
    Dim c As Range

    Selection.NumberFormat = "@"
    For Each c In Selection
        c.Value = Trim(c.Value) & ""
    Next c
End Sub

The idea behind the macro is that you would select all the part numbers and then run it. The formatting of the selection is set to text and then each cell within the selection is "re-entered" so that numeric values are subsequently treated as text. Using the Trim function and adding an empty string to the end of what you "re-enter" forces Excel to treat the information as text.

If you are not adverse to using helper columns and you don't want to use a macro, you might try creating one that contains the textual equivalent of your part numbers. This could be done with any of the following formulas:

=A1 & ""
=CONCATENATE(A1)
=TEXT(A1, "@")

Copy the formula down as many cells as necessary. These formulas force the contents of the helper column to be treated as text. You could then sort your part numbers based on the contents of the helper column.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (4627) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Not Enough Resources to Delete Rows and Columns

Few things are as frustrating as trying to delete rows or columns and having Excel tell you that you can't perform the ...

Discover More

Using Call to Run VBA Macros

An elegant way to run macros from within macros is to use the Call statement. In order to use it, you need to provide a ...

Discover More

Excel Not Responding

Have you ever been working with data in Excel and experienced a "freeze" where the program stops responding? This can be ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Sorting while Ignoring Leading Characters

Want to ignore some characters at the beginning of each cell when sorting? The easiest way is to simply create other ...

Discover More

Ignoring Selected Words when Sorting

If you use Excel to maintain a list of text strings (such as movie, book, or product titles), you may want the program to ...

Discover More

Fixing Odd Sorting Behavior

When you sort data that contains both numbers and text, you may not get exactly the result that you expected. To know ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 7?

2024-01-08 17:35:22

J. Woolley

The new SortPlus described in my most recent comment below is a user-defined function (UDF) intended for application in a cell formula. It is slower but more capable than Excel's SORT because SortPlus uses the Range.Sort method of Excel's object model. This implies that SortPlus uses the values from its RangeArray argument to populate a Worksheet.Range object. But a UDF is restricted from doing any of the following:
+ Insert, delete, or format cells on the spreadsheet
+ Change another cell's value
+ Move, rename, delete, or add sheets to a workbook
+ Change any of the environment options
+ Add names to a workbook
+ Set properties or execute most methods
So you might wonder how SortPlus can populate a Worksheet.Range and apply that Range's Sort method.
This is how. I created a virtual worksheet in a phantom workbook in an independent Excel application within my UDF. Here is an abstract:

Function SortPlus(...) As Variant
    ...
    Dim App As Excel.Application
    Set App = New Excel.Application
    With App
        .Visible = False
        With .Workbooks.Add 'phantom workbook
            With .Worksheets(1) 'virtual worksheet
                With .Range("A1").Resize(nRows, nCols)
                    .Value = ... 'populate
                    .Sort ...
                    SortPlus = .Value 'result
                End With
            End With
            .Close False
        End With
        .Quit
    End With
    Set App = Nothing
    ...
End Function

It is important to note that App is independent of the host Excel application; they do not share properties or restrictions.
I have not seen this technique used in a UDF before. If you are aware of an earlier reference, please identify it in a comment here.
See https://sites.google.com/view/MyExcelToolbox
and https://support.microsoft.com/Search/results?query=limitations+of+custom+functions+in+Excel


2024-01-07 11:09:54

J. Woolley

My comment dated 2023-09-19 below described My Excel Toolbox's SortPlus function. That function has been replaced by a new version that no longer requires Excel 2021 or later (Excel 2021+) because it does not use Excel's SORT function. The new version enables 3 additional optional parameters as follows:
=SortPlus(RangeArray, [SortIndex], [SortOrder], [LeftRight], [HasHeader], [CaseSensitive], [CodeOrder], [CustomOrder], [NumberAsText], [TextAsNumber])
The first 7 parameters are described in my previous comment. The 3 new parameters are described here.
CustomOrder is FALSE (default) to ignore, or a custom list number (like 1 for Sun, Mon, Tue,...), or a temporary custom list as comma separated text (like "N, S, E, W"). Backslash (\) can be used to escape comma in CustomOrder comma separated text.
NumberAsText is FALSE (default) to ignore; if TRUE and RangeArray is a cell range, then numeric cell values displayed as text are considered text.
TextAsNumber is FALSE (default) to sort numeric text separate from numbers or TRUE to treat numeric text as numbers.
CodeOrder applies only if CustomOrder and TextAsNumber are both FALSE.
SortPlus is slower but more capable than Excel's SORT and does not require Excel 2021+. If used in a cell formula, it returns a dynamic array. When using earlier versions of Excel without support for dynamic arrays, review the PDF file UseSpillArray.pdf.
See https://sites.google.com/view/MyExcelToolbox


2023-09-22 17:24:01

J. Woolley

Re. my most recent comment below, the SortANSI macro now supports Undo (Ctrl+Z).


2023-09-21 16:29:56

J. Woolley

My Excel Toolbox now includes the SortANSI macro to sort all rows of a selected range by a specified column in ascending or descending order. The text portion of that column will be sorted in ANSI/ASCII/Unicode order, either case ignored or case sensitive, instead of Excel order. SortANSI will not sort a range that has formulas and does not support Undo (Ctrl+Z).
See https://sites.google.com/view/MyExcelToolbox


2023-09-19 14:45:29

J. Woolley

My Excel Toolbox now includes the following function to enhance the SORT function available in Excel 2021+:
=SortPlus(RangeArray,[SortIndex],[SortOrder],[LeftRight],[HasHeader],[CaseSensitive],[CodeOrder])
The first four arguments match the SORT function; the remaining three are added features. All but the first are optional.
RangeArray can be a cell range (contiguous) or an array constant or the result of an array function.
SortIndex is the base-1 index of a column or row to sort by; default is 1.
SortOrder is 1 (default) for ascending or -1 for descending.
LeftRight is FALSE (default) to sort rows or TRUE to sort columns.
HasHeader is FALSE (default) to sort the first row/column or TRUE to skip the first row (if LeftRight is FALSE) or first column (if LeftRight is TRUE).
CaseSensitive is FALSE (default) to sort text with case ignored or TRUE for a case sensitive sort.
CodeOrder is FALSE (default) to sort text in Excel order or TRUE to sort text in ANSI/ASCII/Unicode order. (See my most recent coment below.)
Text that looks like a number is sorted as text, not as a number.
The following example returns a case sensitive sort in ANSI/ASCII/Unicode order of a multi-column range with no header row by column 3 descending:
=SortPlus(D1:H99,3,-1,,,TRUE,TRUE)
The next example sorts an array function result that includes a header first by column 1 then by column 2, both ascending:
=SortPlus(SortPlus(ListDocProperties(),1,,,TRUE),2,,,TRUE)
SortPlus requires Excel 2021 or later (Excel 2021+). It replaces the SortCaseSensitive function removed from My Excel Toolbox.
See https://sites.google.com/view/MyExcelToolbox


2023-09-18 11:56:25

Hans

You could do a reverse after sorting that deletes the final space


2023-09-18 11:28:53

J. Woolley

Re. Steve Alford's initial comment below, it should be noted that the Windows version of Excel does NOT sort text characters in ANSI/ASCII/Unicode order. (Ditto for other Windows apps like Explorer.) The sort order is illustrated in the following table (see Figure 1 below) which is divided into three sections for convenient display. Each section has the following four columns:
1. The code value in ANSI/ASCII/Unicode order
2. The character for that code value: CHAR(col.1)
3. Column 2 sorted by Excel BEFORE dividing into three sections: SORT(col.2)
4. The ANSI code value of the character in column 3: CODE(col.3)
In column 3 notice that Excel sorts apostrophe and hyphen before space, all special characters are before numerals, and upper/lower case letters are together (A a B b C c ...) instead of separate (A B C ... a b c ...) as in ANSI/ASCII/Unicode.
With respect to letters, case sensitive Excel order moves each lower case before its upper case (a A b B c C ...) while case sensitive ANSI/ASCII/Unicode order keeps all upper case before lower case (A B C ... a b c ...).
With respect to VBA:
+ Option Compare Text or StrComp(s1, s2, vbTextCompare)
-- corresponds to Excel order with case ignored.
+ Option Compare Binary or StrComp(s1, s2, vbBinaryCompare)
-- corresponds to case sensitive ANSI/ASCII/Unicode order.
Excel's sort order becomes more curious when special characters are combined with letters like X'X or X-X or X:X or X?X or even XxX etc. In this case, the combinations with apostrophe or hyphen are sorted LAST when other things are equal (except perhaps the total number of characters). This explains why Excel sorted C-00958-501 after C00958-501 and C-00958-503 after C00958-503 in Steve Alford's initial comment. If Steve replaced all hyphens with something other than apostrophe he would see the result he expected when sorting in Excel order; on the other hand, the ANSI/ASCII/Unicode result would depend on his choice of replacement character. Replacing all hyphens with tilde (the last ASCII character) yields the following results (see Figure 2 below)

Figure 1. 

Figure 2. 


2023-09-10 14:35:53

J. Woolley

Re. my most recent comment below, the SortSelection macro now uses an insertion sort algorithm instead of the previous quick sort. Insertion sort is more appropriate for a partially sorted array. The new version can be viewed or downloaded as text at Google Drive:
https://drive.google.com/file/d/1--WE5sQJQcdgsgdBlIVyVgIJwqZ3-0iZ/view


2023-09-03 16:12:42

J. Woolley

Re. my most recent comment below, improved versions of the SortSelection and Quicksort procedures can now be viewed or downloaded as text at Google Drive:
https://drive.google.com/file/d/1--WE5sQJQcdgsgdBlIVyVgIJwqZ3-0iZ/view


2023-08-28 18:16:23

J. Woolley

Steve:
I have a Quicksort solution to your first query but have not been able to post it here as a comment. You can view and download a text file at Google Drive:
https://drive.google.com/file/d/1--WE5sQJQcdgsgdBlIVyVgIJwqZ3-0iZ/view


2023-08-24 14:53:57

Steve

Peter,

I get that the LEFT(A2,2) will sort it properly, but only if it's pre-sorted A-Z first. If the list has C-00958-503 before C-00958-501 and you sort using the left 2 characters, the sort fails to sort the -501 before the -503 (ignoring the last "-'"). I'm not smart enough to understand that all variances will be sorted in ascii sequence if the data is always sort A-Z before sorting using LEFT(A2,2). It's possible that I'm totally missing your response, however, I appreciate your help.


2023-08-23 04:41:04

Peter Atherton

Steve

Add a column with the 1st two letters of each part number and sort on that colunm, e.g. LEFT(A2,2)

{[fig}]


2023-08-22 09:40:19

Steve Alford

Allen, I'm also in the Aviation Industry, but can not make any of the sorting solutions actually sort via the ascii table. For example:

Before Sort After Sort
=======================================
BUM7004-1 BUM7004-1
BUM7004-501 BUM7004-501
BUM7011-537 BUM7011-537
C00948-1 C-00958-501
C00948-503 C-00958-503
C00958-501 C00948-1
C-00958-501 C00948-503
C00958-503 C00958-501
C-00958-503 C00958-503
D10-21-021-501 D10-21-021-501

I can only get the sort in the Before Sort column. I understand that your solution does not mention ascii order, but your solution did come up when searching for ascii sort order. Any ideas on how to do a true ascii order sort.

Thanks,
Steve


2023-07-30 11:19:40

J. Woolley

Kris might not like the result when numeric values are sorted as text. Here is an example (including negative values):
1,-1,10,-10,2,-2,20,-20,200,3,....
My Excel Toolbox includes the TextFromNumber macro to convert numeric constants in a selection to text; other cells in the selection are not disturbed. The result can be either fixed-length padded with leading zeros or variable-length. The fixed-length option includes a decimal point and any minus sign in the count, so converting the previous example to 6 characters would sort:
00001.,00002.,00003.,-0001.,00010.,-0002.,00020.,-0010.,-0020.,00200.,....
See https://sites.google.com/view/MyExcelToolbox


2023-07-29 16:18:19

J. Woolley

To convert cells in a column to text:
1. Select the cells
2. Pick Data > Text to Columns
3. In the Text to Columns Wizard:
3.a. Pick Delimited, then click Next
3.b. Clear all delimiters, then click Next
3.c. Pick Text for column data format, then click Finish


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.