by Allen Wyatt
(last updated October 14, 2017)
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:
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.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
When formatting the layout of your worksheet, Excel allows you to easily merge adjacent cells together. This can cause ...Discover More
One way you can easily work with data in a worksheet is to sort it into whatever order you find most helpful. Excel ...Discover More
If the font color used for the data in your worksheet is critical, you may at some time want to move cells that use a ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.