Numbers to Text in Excel
MS Excel, or Microsoft Excel, is powerful spreadsheet software used to record data in cells across multiple worksheets. Worksheets allow users to enter various financial and analytical data. Users can make data entries and perform calculations or operations. Excel sheets act like a database using which users can create dashboards or analytical reports. Although Excel supports almost all types of data, numbers are the most common data used in Excel. However, there may be scenarios when we might need to convert numbers to text for various purposes.
This tutorial discusses the various step-by-step methods to convert numbers to text in Excel. Before moving on to the conversion methods, let us first understand how we can identify or differentiate between the numbers and text in Excel.
Identifying/ Differentiating between Numbers and Texts in Excel
The following are some of the common visual indicators for differentiating numbers and text in Excel:
It is not compulsory to have all the visual indicators displayed in the sheet to differentiate between numbers and text. We can find one or more indicators accordingly. In the following image, we can see the differences between number (left-side) values and the text (right-side) values:
How to Convert Numbers to Text in Excel?
We can use several methods when we need to perform any specific task or operation in Excel. Similarly, when converting numbers to text, Excel provides numbers of methods. Some methods are the fastest and easiest to use, while others are comparatively lengthy and complicated. However, we should know all the handful of ways to convert numbers to text as we may need to try different methods depending on how the numbers in corresponding cells are formatted.
Following are the most common and effective methods used to change or convert numbers to text in Excel:
Converting Numbers to Text by using/ adding an Apostrophe
The fastest and simplest way to convert any number to text in Excel is to insert/include an apostrophe before the number in the corresponding cell. When we add an apostrophe in front of the desired numbers, it is not displayed in the cell. Instead, the apostrophe is displayed in the formula bar only after selecting the particular cell(s).
By adding an apostrophe, we instruct Excel to treat the contents of the corresponding cell as text. This is the useful and shortest way to force Excel to display the number as text in the desired cell.
In the following image, we can see that the numbers are converted to text by adding an apostrophe:
Converting Numbers to Text by using Text to Columns
Another quick method to convert numbers to text involves using Excel's Text to Columns tool. This tool is mainly used to split the data of a cell into multiple columns in a worksheet. However, it also provides specific settings that help us select the desired cell and convert numbers to text with just a few clicks.
To convert the desired numbers to text using the Text to Columns tool, we must perform the following steps:
This method is mainly useful when we need to convert the numbers of the entire column into text. In this way, we can convert the values in bulk. In other cases, we can try other methods.
Converting Numbers to Text by using the Excel Ribbon
Excel ribbon consists of many options or tools under various tabs. These tabs contain almost all the existing Excel features. We can use the Excel ribbon to change or convert numbers into text for the desired cells. This method is useful when we have multiple cells to change in different columns. We don't need to edit each cell and insert an apostrophe with this method. Instead, we can convert numbers into text at once.
According to this method, we need to change the cell format from the ribbon and set the desired cells as text. We can set the specific cells to text only even when they are empty. So, whenever we enter the numbers in those cells, the numbers will be treated as text.
Following are the steps to use this method:
Converting Numbers to Text by using the Format Cells
Converting the numbers into text is also possible from the Format Cells dialogue box. It is the most consistent and reliable method of converting numbers to text. Like the previous method, we typically change the cell format of the corresponding cells and set them to act as text only.
We must follow the below steps to use the Format Cells dialogue box to convert numbers into Text within the Excel worksheet:
Converting Numbers to Text by using the Function
Excel provides various built-in functions and formulae using which we can perform specific operations. Although there are several ways to help us convert numbers to text, Excel formulas can make it easier. To perform conversion of numbers into text, we can take advantage of Excel's TEXT function.
The main advantage of the TEXT function is that it converts numeric values into the text and allows us to specify how the particular value will be displayed in the worksheet. This means that we can use the TEXT function when we need to display numbers in a customized readable format or if we want to associate digits with text or symbols. However, when using this particular method, we need to use other cells, rows, or columns to get the converted values from the source cells.
To apply the TEXT function to convert numbers to text, we must perform the following steps:
The second argument in the TEXT function allows us to control how the number needs to be formatted before it is converted into text. We can use different ways to adjust the second argument based on the original number. For example:
Similarly, we can try other custom codes in the second argument to display a number differently.
Why do we need to Convert Numbers to Text in Excel?
When we convert numbers to text in Excel, the entered value or number does not change. By conversion, Excel only changes the way of reading the particular values. After the values are converted from numbers to text, Excel reads them as text and restricts calculations or auto adjustments.
Following are some of the scenarios when we might need to convert numbers to text:
Adding/ Keeping Leading Zeros
Leading zeros (zeros added before a number) are essential elements of Excel. Although we don't usually need to use them in worksheets, we may need them in some specific cases. By default, when we enter values with leading zeros in Excel, the values are automatically changed, and zeros are removed or deleted. For example, if we enter a value like '0001' in an Excel cell, it will change to '1'.
Therefore, we must format the corresponding cells as text when needing leading zeros. However, we cannot use these values in arithmetic calculations when using text format to use leading zeros.
Entering Large Numeric Values
When we enter large numbers or numeric values in Excel, the values are automatically changed to their exponential forms. The values may not display as we needed. Therefore, if we want to display the large numbers like the account numbers, employee ID, order ID, or any other large number, we must change the corresponding Excel number cells to text only.
Preventing Numbers to Dates
One common scenario where we may need to convert numbers to text is when we need to enter specific numbers (e.g., scores) in Excel. For example, 01-01, 20-01, 01-09, etc. By default, Excel reads all such numbers as a date and converts them into valid date formats without asking users. Therefore, when we need to enter scores or relevant numbers in Excel, we can change the numbers into text.
Important Points to Remember
Next TopicText to Numbers in Excel