While working with large Microsoft Excel, sometimes we need to convert numbers to text. We can convert the numbers to text with 2 decimal places. We can easily do that from our dataset. Today, in this article, we’ll learn five quick and suitable ways to convert number to text with 2 decimal places in Excel effectively with appropriate illustrations.
How to Convert Number to Text with 2 Decimal Places in Excel: 5 Suitable Ways
Let’s consider a situation where we have an Excel worksheet that contains information about different fruits and their price. We will use this worksheet to show how to convert numbers to text with 2 decimal places in Excel. We will use the apostrophe signs (‘), Format Cells command, TEXT and IF functions, and VBA macros to convert numbers to text with 2 decimal places. Here’s an overview of the dataset for today’s task.
1. Use the Apostrophe Symbol to Convert Number to Text with 2 Decimal Places in Excel
The easiest way to convert numbers to text with 2 decimal places in Excel is to insert apostrophe signs (‘) in front of the numbers. From our dataset, you have to put an apostrophe sign (‘) manually in front of the price of Apple, and while inserting the apostrophe sign (‘) in front of the second number and the rest, it will take the apostrophe signs (‘) automatically to convert numbers to text with 2 decimal places in Excel. Let’s follow the steps below to learn!
Steps:
- We will put an apostrophe sign (‘) in front of each number That will convert the number to text with 2 decimal places.
Read More: How to Convert Number to Text with Commas in Excel
2. Apply the Format Cells Command to Convert Number to Text with 2 Decimal Places in Excel
Another easy way to convert numbers to text with 2 decimal places in Excel is by applying the Format Cells command. It is time-saving also. Let’s follow the instructions below to learn!
Step 1:
- First of all, copy the price of the products by applying the Ctrl + C as the keyboard shortcut from column C and paste those values in column D by using the keyboard shortcut Ctrl + V.
Step 2:
- After that, press Ctrl + 1 simultaneously on your keyboard. A Format Cells dialog box will appear in front of you. From the Format Cells dialog box, firstly, select the Number Secondly, choose Text from the Category box. At last, press OK.
- After completing the above process, you will be able to convert numbers to text with 2 decimal places that have been given in the below screenshot.
3. Convert Number to Text with 2 Decimal Places Using the TEXT Function in Excel
The easiest way is to use the TEXT function in Excel to convert numbers to text with 2 decimal places. Let’s see how we can do that.
Step 1:
- First, we will select a cell in the Price column to add decimal points to that cell value. For example, we have selected cell C13.
- Then, we will right-click on that cell. A menu will appear. We will click on the Format Cells from that window.
- A window titled Format Cells will now appear. We will increase the decimal places to 2. Then, we will click on OK.
- We will increase the decimal places to different numbers for different values of the Price.
Step 2:
- Then, we will write the following formula in cell D5.
=TEXT(C5,"##0.00")- The TEXT function allows you to modify the appearance of a number by using format codes to apply formatting to it.
- After that, simply press ENTER on your keyboard and you will see that the number in cell C5 has been converted to text with 2 decimal places.
- Hence, we will drag the fill handle of cell D5 to apply the formula to the rest of the cells.
- Finally, we will be able to convert all the numbers in the Price column to text with 2 decimal places which have been given in the below screenshot.
4. Use the IF Function to Convert Number to Text with 2 Decimal Places in Excel
Alternatively, we can also use the IF function to convert numbers to text with 2 decimal places in Excel. Let’s follow the instructions below to learn!
Steps:
- First of all, we will add decimal points to the cell values in the Price column as we have done in the third method.
- Then, we will write the following formula in cell D5.
=IF(CELL("FORMAT",C5)="F2", TEXT(C5,"0.00"), TEXT(C5, 0))- IF function will check if the number has 2 decimal points.
- If the number has 2 decimal points, the TEXT function will convert the number to text while keeping those 2 decimal points with the number.
- And if the number does not have 2 decimal points, then the TEXT function will convert the number to text without changing anything.
- Hence, press ENTER on your keyboard and you will be able to convert the number 50 into text 420.50 as the return of the IF function.
- Then, we will drag the fill handle of cell D5 to apply the formula to the rest of the cells.
- Finally, we will see that all the numbers in the Price column have been converted to text with 2 decimal places.
5. Run a VBA Code to Convert Number to Text with 2 Decimal Places in Excel
In this method, we will convert numbers to text with 2 decimal places by applying the VBA Code. Applying the VBA code to convert numbers to text with 2 decimal places is the easiest and the time-saving also. Let’s follow the instructions below to learn!
Step 1:
- First of all, select the number range cell C5 to C13, then, from your Developer tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Convert Number to Text will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
Step 2:
- Hence, the Convert Number to Text module pops up. In the Convert Number to Text module, write down the below VBA code.
Sub Decimal_Places()
Dim R As Range
Set R = Selection
R.NumberFormat = "#,##0.00;-#,##0.00"
End Sub
- After that, run the VBA To do that, go to,
Run → Run Sub/UserForm
- After running the code, you will be able to give the number in a format with two decimal places.
Step 3:
- Now, we will write down the same TEXT formula we have used in method 3 in cell D5 to convert the number in cell C5 to text.
- Upon dragging the Auto Fill handle downward to apply the formula to the rest of the cells, we will see that all the numbers in the Price column have been converted to text with 2 decimal places.
Things to Remember
👉 To open the VBA editor Press ALT + F11. You can press ALT + F8 to bring up the Macro window.
👉 If you do not have a Developer tab, you can make it visible in the following way,
- File → Option → Customize Ribbon
Download Practice Workbook
Conclusion
I hope all of the suitable methods mentioned above to convert numbers to text with 2 decimal places will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
Related Articles
- Excel Convert Number to Text with Leading Zeros
- How to Convert Number to Text with Green Triangle in Excel
- How to Convert Number to Text for VLOOKUP in Excel
- Convert Number to Text without Scientific Notation in Excel
- How to Convert Number to Text and Keep Trailing Zeros in ExcelÂ
- How to Convert Number to Text in Excel with Apostrophe
<< Go Back to Excel Convert Number to Text | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
