Sometimes we need to convert Number to Text in Excel so that we can search by part easily or do other operations. In this article, we will discuss how to convert Number to Text with 2 Decimal places in Excel in 6 convenient ways.
📁 Download Excel File
Download the following Excel file to realize the topic more clearly.
Learn to Convert Number to Text with 2 Decimal Places in Excel with 6 Easy Approaches
Using the following dataset, we will accomplish our goal to convert Number to Text with 2 decimal places in Excel.
1. Adding an Apostrophe Symbol to Convert Number to Text in Excel
Perhaps it is the easiest method to convert Number to Text in Excel. However, it is convenient only for a few cells of data.
- Select cell C6 first and copy using Ctrl+C.
- Then select cell D6 and press Ctrl+V.
- Now, edit cell D6 and add an Apostrophe symbol just like the screenshot below, and press Enter.
- Use the Fill Handle Tool to apply it to other cells.
- The number format has now changed to Text format.
- You can see a little green triangle on the top left corner of the cells as well as a Warning sign that shows the Number Stored as Text (see the screenshot below).
📕 Read More: How to Convert Number to Text with Leading Zeros in Excel
2. Utilizing Format Cell Option to Convert Number to Text in Excel
This time we will learn another handy way to convert Number to Text with 2 decimal places in Excel utilizing the Fomar Cell option. Let’s start!
- In the beginning, select the cells you want to convert to text. In this case, we have selected cells D6 to D11.
- Once you have selected the cells, right-click on your mouse.
- From the Menu, select the Format Cells options.
- A Format Cells dialog box will appear on the screen.
- At this time, click Number and Text from the Category section.
- Now, click OK.
- Voila! The selected column is now in Text You can check that from the Number of the Excel Ribbon just like in the photo below.
📕 Read More: 3 Ways to Convert Number to Text with Commas in Excel
3. Employing TEXT Function to Convert Number to Text in Excel
Alright. It is time to use the TEXT Function to convert Number to Text in Excel.
- First, select cell D6 to enter the following formula:
- Use the Fill Handle Tool like the picture attached below and double-click.
- The formula is applied to all the other cells magically.
You have successfully converted Number to Text using the TEXT Function.
4. Using Text to Columns Wizard
Text to Columns Wizard is a very convenient way to convert Number to Text in Excel. Let’s see how to accomplish that.
- Select the cells that you want to convert to text. We have selected cells D6 to D11.
- After that, go to the Data tab and click on the Text to Columns option under Data Tools.
- A Convert Text to Columns Wizard will open.
- Make sure the Delimited option is checked and click Next.
- On Step 2 of 3, click Next.
- Check the Text button and click Finish on Step 3 of 3.
- As you can see from the picture attached below, the selected column is now in text format.
- Notice that a warning sign is present just beside cell D6. If you click on that, it will show you that the Number Stored as Text.
5. Implementing IF Function
Our 5th way to convert Number to Text with 2 decimal places in Excel is to implement the IF Function.
- Initially, select cell D6 and Enter the following formula:
=IF(CELL("FORMAT",C6)="F2", TEXT(C6,"0.00"), TEXT(C6, 0))
🔨 Formula Breakdown
=IF(CELL(“FORMAT”,C6)=”F2″, TEXT(C6,”0.00″), TEXT(C6, 0))
- First, let’s see how the CELL Function works. Here, CELL Function returns the Number Format of cell C6 e.g. General, Number, Short Date.
- The IF Function checks if the Number Format of cell C6 is F2 (Number up to 2 Decimal Places). If that turns out true, the IF Function will return the number in 2 decimal places in Text format to cell C6. If the Number Format is not F2, the IF Function returns the number in Text format without any decimal places.
- Next, use the Fill Handle Tool from the bottom right corner of cell D6.
- The cells are in text format now.
📕 Read More: 6 Easy Tricks to Convert Date to Text (YYYYMMDD) in Excel
6. Executing Excel VBA Code
Our final method is to execute a VBA code to convert Number to Text with 2 decimal places in Excel.
- Like the screenshot below, select cells D6 to D11.
- Navigate to the Developer tab and select Visual Basic.
- This time, select Insert from the Microsoft Visual Basic for Applications window.
- Click Module.
- A code window will open.
- Enter the following code.
Sub Convert_Number_to_Text() Dim ran As Range Set ran = Selection ran.NumberFormat = "@" End Sub
- From the Run tab, click Run Sub/UserForm.
- Finally, you will find the selected cells are in Text format like the photo below.
📝 Takeaways from This Article
📌 First, we learned a method to convert Number to Text in Excel just by adding an apostrophe symbol.
📌 Secondly, we have changed Number to Text applying the Format Cells option.
📌 Next, the Text to Columns Wizard was exploited to convert Number to Text.
📌 Then, you have used the TEXT Function to achieve our goal
📌 Similarly, the IF Function helped us to convert Number to Text.
📌 Finally, we executed an Excel VBA code to convert Number to Text very easily.
That concludes the discussion for today. These are some convenient methods to convert number to text with in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.