Forcing Excel to Sort Cells as Text

by Allen Wyatt
(last updated October 14, 2017)

7

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 = 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.

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, and 2016.

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

Switching Editing Location

Excel allows you to edit the contents of a cell in two places—the cell itself or in the Formula bar. If you want to ...

Discover More

Displaying the Print Dialog Box in a Macro

Want to print a document by using a macro? One way is to display the Print dialog box and allow the user to interact with it. ...

Discover More

Excel 2016 Filters and Filtering (Table of Contents)

Excel provides two ways to filter your data so that only what you want to see is displayed. Discover how filtering works and ...

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)

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 what is ...

Discover More

Moving Cell Borders when Sorting

Sort your data and you may be surprised at what Excel does to your formatting. (Some formatting may be moved in the sort and ...

Discover More

Separating Cells Based on Text Color

If the font color used for the data in your worksheet is critical, you may at sometime want to move cells that use a ...

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}] 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 4?

2017-10-17 18:26:12

Peter Atherton

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


2017-10-16 12:21:20

Roy B

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.


2017-10-16 05:41:47

DaveS

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.


2017-10-16 04:49:56

Tim W

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


2017-10-15 06:47:38

Alex B

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"


2017-10-15 04:20:51

Louis LAFRUIT

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.


2017-10-14 07:37:57

Alex B

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.


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.