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:

• By default, the numeric values are aligned to the right side of the cell. However, the text values are aligned to the right side of the cell.
• If we select more than one numeric cell in Excel, the status bar at the bottom displays the Average, Count, and Sum for the respective values. In the case of text-formatted cells, the Excel’s status bar only displays the Count.
• Arithmetic operations work as expected for numeric cells. However, the text-formatted cells return errors even though the values seem like numbers.
• When we select the number-formatted cell (s), the formula bar displays the same value as in the cell. For text-formatted cells, the formula bar displays a leading apostrophe in most cases.
• When we select the cell with numeric values, there is no error indicator comes up. However, the error indicator is displayed if we select the text-formatted cell. The error indicator contains an exclamation mark, an error message, and a few quick options inside the drop-down menu. Also, a tiny green triangle is displayed in the top-left corner of the text-formatted cells.
• The Number Format drop-down box displays the ‘Number’ or ‘General’ format on the ribbon under the Home tab after selecting the numeric cells. In the case of text-formatted cells, the format box displays Text.

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:

• First, we need to select a specific column with cells containing numbers that we need to convert into text.
  
• Next, we need to go to the Data tab and click the Text to Columns button under the Data Tools section. This will open ‘Convert Text to Column Wizard’.
  
• In the next window (or wizard), we must click the Next button in Steps 1 and 2. In Step 3 of the Convert Text to Column Wizard, we must select the Text option under ‘Column data format’. It looks like this:
  
• Lastly, we must click the Finish button in the wizard box. After that, all the numbers in the corresponding column will be immediately converted to text or string.
  

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:

• First, we need to select specific cells, range of cells, row, or column where we need to apply text format.
• Next, we need to navigate the Home tab and click on the format drop-down list under the Number section, as shown below:
  
• We must select the Text option from the list, and all the selected cells will be converted to text.
  
  After that, if we enter numbers into such cells, Excel doesn’t apply number rules to the corresponding cells.

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:

• First, we need to select all the effective cells where we want to use numbers in the text format.
• Next, we must press the right-click button via the mouse and select the Format Cells option. Alternately, we can use the keyboard shortcut ‘Ctrl + 1’ to quickly open the Format Cells dialogue box, as shown below:
  
• In the Format Cells dialogue box, we need to select the Number tab and click the Text option under the Category section. After that, we must click the OK button.
  
  This will change all the selected cells’ format to text-based cells. Any number lying on these cells will be treated as text.

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:

• First, we need to select the cell (other than the source cell containing the number) where we want to record the converted text value. For example, if we have numbers in any specific column, we can add a new column next to it to apply the TEXT function and record text values.
• After selecting the cell, we need to type or insert the TEXT function in the respective cell in the following way:
  =TEXT(Cell Reference of Text-formatted Number, “0”)
  It means that if we have a numeric value in a cell B2, we must enter the TEXT function to the destination cell (e.g., cell C2) like this:
  =TEXT(B2, “0”)
  
• After that, we have to press the Enter key. If we have multiple cells, we can copy-paste the formula in the case of relative cells or insert it in non-relative cells accordingly.
  
  After applying the TEXT function in the particular cell, the resultant value is aligned to the left side of the cell. However, it is not yet completely converted to text. This resultant value acts as a formula. Therefore, we need to convert this formula to a value.
• Next, we need to select the resultant cell (s) again. We must press the keyboard shortcut ‘Ctrl + C’ to copy all the selected cells. After copying them, we must press the keyboard shortcut ‘Ctrl + Alt + V’ to open the Paste Special dialogue box.
• We must select the radio button associated with Values in the dialogue box and click the OK button.
  
  This will convert the formulas into text values. The image below shows the tiny green triangle in the top-left corner of the resultant cell, which means that the values are text format.
  
  If needed, we can copy-paste the resultant text-formatted numbers to the source cell and delete them.

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:

• If we enter =TEXT(123,”00000″), we get the result 00123.
• If we enter =TEXT(123.50,”0″), we get the result 123.
• If we enter =TEXT(123.50,”0.0″), we get the result 123.5.
• If we enter =TEXT(123.50,”0.00″), we get the result 123.50.

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

• We must be cautious while selecting the Excel cells to convert numbers to text. To select contiguous Excel cells, we can click on the first and last cell of the desired range while holding down the Shift key. To select non-contiguous Excel cells, we must click on each cell separately while holding down the Ctrl key on the keyboard.
• After converting the Numbers into Text, the values will look almost the same in most cases. However, we will not be able to perform arithmetic calculations, create charts, or other number-based functions.