Converting numbers in Excel to text is a common practice and has many benefits. Let’s look at two very common scenarios:
Scenario #1 – Preserving leading zeroes
If you are entering part numbers, employee IDs, postal codes, etc… there is a high likelihood that the sequence of numbers may have leading zeroes.
- Part # – 004822 (industrial grade widget)
- Employee # – 0003889 (Fred Smith)
- Postal code – 06390 (Fishers Island, New York)
If you enter these values into Excel, all of the leading zeroes will be dropped
Excel does not display insignificant zeroes, i.e. zeroes not acting as placeholders between two significant (non-zero) digits. These insignificant zeroes are simply dropped. In the case of part numbers, employee numbers, postal codes and other such examples, those leading zeroes are important and should not be dropped.
Scenario #2 – Preventing “accidental math”
If I were to list a series of numbers and I had the year “2018” as the title, when I invoke an AutoSum command to add all of the numbers together I don’t want the function to select the year “2018” thinking it is a value to be included in the summation. By storing the year as text the AutoSum function will ignore the column heading.
Two Techniques for Converting Numbers to Text
Technique #1 – Adding single quotes
A very common practice when converting numbers to text is to add the single quote (or apostrophe) character to the beginning of the number.
Although this is a very easy way to perform the conversion, it looses its practicality when entering in large quantities of values. Imagine having to enter in 100,000 part numbers and they all have a leading zero. That’s 100,000 additional keystrokes just to prevent the loss of the leading zeroes. Here’s where a different technique comes to the rescue.
Technique #2 – Conversion through number styles
If the cells containing the values (i.e. part numbers) are selected PRIOR to data entry and formatted with the TEXT style, all of the values entered in those cells from that point forward will be treated as text. This will preserve any leading zeroes and exempt all values in those cells from any common aggregation functions.
- select the cells that will contain the part numbers
- click the down arrow next to the Number Formatting tool on the Home ribbon
- select “Text” located at the bottom of the dropdown
VERY IMPORTANT POINT
When using the number styles approach, the cells must be converted BEFORE the data is entered. If you were to take an existing list of postal codes and apply the Text number style to them, you will not magically see leading zeroes appear before 3-digit or 4-digit postal codes. Likewise, a list of part numbers would not have any leading zeroes inserted. The reason for this is that Excel does not understand what these numbers represent or how many zeroes are missing from the entered values..
Good news; there is a way convert existing lists of numbers
If you have an existing list of values that should display a fixed number of positions, you can create a custom number style to fill in any missing digits with zeroes. In the following example, all part numbers must be 4 digits in length.
- select the cells that will contain the part numbers
- click the down arrow next to the Number Formatting tool on the Home ribbon
- select “More Number Formats…” located at the bottom of the dropdown
In the Format Cells dialog box, select “Custom” from the category list on the left. Click in the field located below the Type: heading and type “0000”.
And there you have it; a list with all of the needed characters.
