Convert Numbers to Text in Excel (Table of Contents)
How to Convert Numbers to Text in Excel
We often faced situations to deal with numbers on formatting the excel sheets or doing calculations with the provided data. There are different ways to convert numbers into text.
Steps to Convert Numbers to Text in Excel
Below mentioned is the easiest way to convert Numbers to Text in Excel. If you have only two or three cell data which needs to be converted into text, these are the simple steps you can follow.
Step 1 – By double-clicking select the cell which you want to change the data and enter an apostrophe before the number that is entered in the cell.
Step 2 – Press enter key which will move the selection into the below cell and the data which is a number being changed into text format.
- Once done with the above steps you can confirm whether the number has been changed to the text format or not.
Step 3 – Select the cell which already changed into text, while selecting the cell a message box icon with a drop-down which appears on the left side.
- Once you point on the message box icon the corresponding text will be displayed which shows if the number in the cell is converted into text?
- If you click on the dropdown arrow it will show the given text is already in text format. Or the number is stored in text format.
Step 4 – By selecting the dropdown arrow it is possible to change the text to its old form using Convert to Number.
Examples to Convert Numbers to Text in Excel
These are the following examples to convert numbers to text:
1. Using General Formula
2. Using format cells command
3. Using addon tools
As a simple method, you can use this inbuilt function Text which is shown in the below screenshot and the format is as follows.
- Where value refers to the cell which contains the data you want to convert. And the next part format_text represents in which format you want to see the data.
- In cell C2 please enter the formula =Text (B2, “0”).
- Then press Enter key.
- Drag and fill up to the range that you want to apply. The results as shown below.
- After applying the formula, the values are pasted in the same sheet. The numbers are changed into text format. The green indication in the upper corner of the cell commonly shows when you have entered a text formatted value into the cell. Or when you change the format of any given data to the text format.
- To change the format of the result we can change the formula as =Text(B1, ”000000”) which will result whole data will be shown in six digit numbers. The missing place values will be filled with “0”. If you want to change the whole data into a unique format or a unique number of decimals, then this format is preferred.
- In the above example, the column Data1 consists of integer numbers with a different number of decimal places. So here we want to make it in a unique way. To change the given numbers into a similar number of decimal values we have applied the formula. In the format, it has given 6 decimal places =Text(B1, ”000000”). So the numbers are changed to 6 digit numbers and the missing decimal places are filled with zero.
To convert Numbers to Text in Excel with different decimal points the changes to do with the formula is
=Text(B1,”00.000’). Which will keep the given data into a similar format in such a way number of digits’ after the decimal point? The figure is given shows the result after applying the formula in the given data.
In this example, we want to make the number of digits after decimal point into a unique number. So the formula is changed into =Text(B1, ”00.000’. This will show 3 digits after the decimal point.
Using Format Cells Command
Convert Numbers to Text in Excel using Format cells command is also recommended. By right-clicking the selected cell it will get an option Format Cells. This will lead to another window which offers all type of data that we are using like number, date, general etc.
- The Format_Cells Command is commonly used to change the data of the given cells into another format according to your recommendation.
Automatically set the data type of empty cells
It is possible to Automatically set the data type of empty cells and set the format of a range of cells in advance before entering the data into the cells.
How to Set the Data Type Automatically in Excel?
The same method is preferred in this case. Below is the screenshot where the format of the empty cells is changed into text. So if you enter the numbers into this cells the data will keep the format as text automatically.
How to Avoid Missing Numbers while Converting Numbers into Text?
To prevent the missing numbers while processing or changing from one sheet to another it is better to keep the numbers as text format.
- In the above, once you press enter after entering the numbers the common result will be as below
- The initial “0” will be lost since you haven’t set any data type to this column. The same will happen when you try to copy paste the same numbers from this sheet to another.
Using Add-On Tools
Different Add-on Tools like Kutools for Excel. These are built-in tools for excel which can be downloaded and used as add-on functions with yours excel.
Step to Download Kutools
- Go to Google and search for free Kutools and download the setup of Kutools from there.
- This provides different options to process your data in any way you want.
- More than the available functions with normal excel here you can find customized options to deal with functions.
- Which shows two different options as Text to numbers and Numbers to Text.
- By clicking the Numbers to Text option you can change the selected numbers to text.
Using Excel Macros
- Excel macros is another option where you can design own customized functions to change the numbers into text.
- If you are good at VBA then go for this method where you can design functions and apply that to the data where ever you want.
- It is possible to create VBA functions like stored procedures /functions. So as like the normal functions can directly apply this to the excel cells.
- Another option is to create separate macro fixing buttons and assign created functions to the macro that you have created
Things to Remember About Convert Numbers to Text in Excel
- If the number starts with “0” be aware of changing the data into text before processing it further. There are chances to lose this initial zero while doing copy paste into another cell
- If you know the data type prior to processing it set the format of the empty cell before you process it
- If you want to keep the numbers as text and found any issues on copy-pasting it into another sheet, keep the data in a note pad and again copy it from the notepad
- Select the better option according to the quantity of data you need to process.
This has been a guide to Convert Numbers to Text in Excel. Here we discuss how to use Convert Numbers to Text in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –