How to Use Format Function in Excel (with Suitable Examples)

In Microsoft Excel, we need to format the values of any cells to get proper readability and a good outlook. Besides using the available built-in command in the Excel ribbon, we can format the cell value by using the TEXT function and also VBA Format function. If you are also curious to know about it, download our practice workbook and follow us.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

10 Suitables Examples of TEXT Function to Format a Cell Value in Excel

Here, we are going to use the TEXT function to convert the formatting of our cell value. We will demonstrate 10 suitable examples to show all of the formattings.

1. Formatting Number Value

In the first example of the TEXT function, we will change the formatting of a number and display it in different formattings. We have a number in cell B5. We are going to format it into 7 distinct formats.

Select a cell and write down any of the formatting formulas according to your desire.

To convert the value into ‘#,###.00’ format, the formula will be:

For converting the value into ‘(#,###.00)’ format, the formula will be:

To convert the value into ‘-#,###.00’ format, the formula will be:

For converting the value into ‘#,###’ format, the formula will be:

To convert the value into ‘###,###’ format, the formula will be:

For converting the value into ‘####.00’ format, the formula will be:

To convert the value into ‘#.00’ format, the formula will be:

Press the Enter key and you will get your desired cell formatting.

Thus. we can say that we are able to use the Excel TEXT function to format numbers.

Read More: How to Combine Text and Numbers in Excel and Keep Formatting

2. Formatting Currency

In the following example, we will use the TEXT function to show a number in the currency format. 7 different types of currency formatting will demonstrate here. We have the number in cell B5.

To get the value, select any cell and write down any of the formulas according to your requirement.

To convert the value into ‘$#,###.00’ format, the formula will be:

For converting the value into ‘($#,###.00)’ format, the formula will be:

To convert the value into ‘-$#,###.00’ format, the formula will be:

For converting the value into ‘￥#,###’ format, the formula will be:

To convert the value into ‘￥###,###’ format, the formula will be:

For converting the value into ‘$####.00’ format, the formula will be:

To convert the value into ‘$#.00’ format, the formula will be:

Press Enter. You will get your desired currency formatting.

So. we can say that we are able to use the Excel TEXT function to format a number into currency.

Read More: Excel Text Function Format Codes

3. Formatting Date

Now, we are going to use the TEXT function to format dates in different formats. Our date value is in cell B5. We will show 9 different types of date formatting.

First, select any cell and write down any of the formulas according to your requirement.

To convert the date into ‘DDMMMYYY’ format, the formula will be:

For converting the date into ‘DDMMMYY’ format, the formula will be:

To convert the date into ‘MMM DD, YYYY’ format, the formula will be:

For converting the date into ‘DDDD’ format, the formula will be:

To convert the date into ‘DDDD,DDMMMYYYY’ format, the formula will be:

For converting the date into ‘DDDD, MMM DD, YYYY’ format, the formula will be:

To convert the date into ‘MM/DD/YYYY’ format, the formula will be:

For converting the date into ‘MM/DD’ format, the formula will be:

To convert the date into ‘YYYY-MM-DD’ format, the formula will be:

Then, press Enter. You will get your desired date formatting.

Hence. we can say that we are able to use the Excel TEXT function to format dates.

4. Formatting Time

Here, the TEXT function will help us to format the time value in different formats. The time which we have to format is in cell B5. We will display 3 different types of time formatting.

At first, select any cell and write down any of the formulas according to your requirement.

To convert the time into ‘H:MM AM/PM’ format, the formula will be:

For converting the time into ‘H:MM:SS AM/PM’ format, the formula will be:

To convert the time into ‘H:MM:SS AM/PM’ format, the formula will be:

We will also use the NOW function.

After that, press Enter. You will get your desired time formatting.

Therefore. we can say that we are able to use the Excel TEXT function to format our desired time.

5. Formatting Combined Date and Time

Sometimes, our dataset contains both date and time in the same cell. In that case, we can format them by using the TEXT function. We will show 3 different types of formatting where the time and date are placed together. The value is in cell B5.

Firstly, select any cell and write down any of the formulas according to your need.

To convert the both date and time into ‘MMM DD, YYYY H:MM:SS AM/PM’ format, the formula will be:

For converting the both date and time into ‘YYYY-MM-DD H:MM AM/PM’ format, the formula will be:

To convert the both date and time into ‘YYYY-MM-DD H:MM’ format, the formula will be:

Next, press Enter. You will notice the value will format according to your desire.

At last. we can say that we are able to use the Excel TEXT function to format date and time together at the same time.

6. Formatting Percentage

We can convert a number into percentages by using the TEXT function. We are going to show 3 different types of formatting. Mainly, the formatting will vary on the number of digits we keep after the decimal point. The value which will convert is in cell B5.

First of all, select any cell and write down any of the formulas according to your requirement.

To convert the value into ‘0%’ format, the formula will be:

For converting the value into ‘0.0%’ format, the formula will be:

To convert the value into ‘0.00%’ format, the formula will be:

Now, press Enter. You will get your desired percentage formatting.

In the end. we can say that we are able to use the Excel TEXT function to format a number into percentages.

Read More: How to Show Percentage in Legend in Excel Pie Chart (with Easy Steps)

7. Formatting Fraction Number

In this example, we will format the fraction numbers using the TEXT function. We are going to demonstrate 9 distinct types of formatting. The decimal number value that will format is in cell B5.

Select any cell and write down any of the formulas according to your requirement.

To convert the value into ‘?/?’ fraction format, the formula will be:

For converting the value into ‘?/??’ fraction format, the formula will be:

To convert the value into ‘?/???’ fraction format, the formula will be:

For converting the value into ‘?/2’ fraction format, the formula will be:

To convert the value into ‘?/4’ fraction format, the formula will be:

For converting the value into ‘?/8’ fraction format, the formula will be:

To convert the value into ‘?/16’ fraction format, the formula will be:

For converting the value into ‘?/10’ fraction format, the formula will be:

To convert the value into ‘?/100’ fraction format, the formula will be:

Finally, press Enter. You will get your desired percentage formatting.

Thus. we can say that we are able to use the Excel TEXT function to format a decimal number into a fraction.

8. Formatting Scientific Number

In the following example, we will format a numeric value into a scientific number through the TEXT function. We are going to format the number based on the number of digits after the decimal points. The original number value that will format is in cell B5.

In the beginning, select any cell and write down any of the formulas according to your desire.

To convert the value into ‘0.00E+00’ format, the formula will be:

For converting the value into ‘0.0E+00’ format, the formula will be:

Press Enter. You will get the formatted value.

Hence. we can say that we are able to use the Excel TEXT function to format a decimal number into a scientific number.

9. Formatting Telephone Number

Now, we will show you the formatting procedure to format general numeric values into a telephone by the TEXT function. The numeric value is in cell B5.

First, select any cell and write down any of the formulas according to your requirement.

To convert the value into ‘(##) ###-###-#####’ format, the formula will be:

Afterward, press Enter. You will figure out the telephone number.

Therefore. we can say that we are able to use the Excel TEXT function to format a decimal number into a fraction.

10. Formatting Zero Leading Number

In the last example, we are going to use the TEXT function format for the numbers which have to start with a Zero (0). We are going to show 3 different formulas for formatting. The number is in cell B5.

At first, select any cell and write down any of the formulas according to your requirement.

To convert the value into ‘00’ format, the formula will be:

For converting the value into ‘000’ format, the formula will be:

To convert the value into ‘0000’ format, the formula will be:

Then, press Enter. You will get your desired percentage formatting.

Finally. we can say that we are able to use the Excel TEXT function to format a decimal number that starts with Zeros.

Overview of VBA Format Function

Format is a VBA function. You cannot find or use it in the Excel spreadsheet. We can use this function only at the time when we write a VBA code.

⏺ Function Objective:

This function is generally used for changing the format of a cell value through the VBA.

⏺ Syntax:

⏺ Arguments Explanation:

⏺ Return:

After running the code the function will show the cell value with our desired formatting.

⏺ Availablity:

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.

5 Suitable Examples of VBA Format Function to Format a Cell Value

Here, we will demonstrate 5 easy examples for showing the format changing of cell values through the VBA Format Function. The examples are shown below step-by-step:

1. Formatting Number

In the first example, we will write a VBA code that will format our numeric number into 5 different types of formats. The steps are given below:

📌 Steps:

• To start the process, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

• As a result, a dialog box will appear.
• Now, in the Insert tab on that box, click the Module option.

• Then, write down the following visual code in that empty editor box.

Sub Format_Number()
Worksheets(11).Activate
  Cells(5, 2) = 123456
  Cells(5, 3) = Format(Cells(5, 2), "General Number")
  Cells(6, 3) = Format(Cells(5, 2), "Currency")
  Cells(7, 3) = Format(Cells(5, 2), "Fixed")
  Cells(8, 3) = Format(Cells(5, 2), "Standard")
  Cells(9, 3) = Format(Cells(5, 2), "Scientific")
End Sub

• After that, press ‘Ctrl+S’ to save the code.
• Close the Editor tab.
• After that, in the Developer tab, click on Macros from the Code group.

• As a result, a small dialog box titled Macro will appear.
• Select the Format_Number option and click the Run button to run the code.

• You will see the number will show in different formats.

Thus, we can say that our visual code works perfectly, and we are able to use the Format function in Excel.

2. Formatting Percentage

In this example, we will write a VBA code to format a cell value with the percentage. The steps of this approach are given as follows:

📌 Steps:

• First of all, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

• A small dialog box will appear.
• After that, in the Insert tab on that box, click the Module option.

• Write down the following visual code in that empty editor box.

Sub Format_Percentage()
Worksheets(12).Activate
    Cells(5, 2) = 0.88
    Cells(5, 3) = Format(Cells(5, 2), "Percent")
End Sub

• Now, press ‘Ctrl+S’ to save the code.
• Then, close the Editor tab.
• Afterward, in the Developer tab, click on Macros from the Code group.

• As a result, a small dialog box titled Macro will appear.
• Next, select the Format_Percentage option.
• At last, click the Run button to run the code.

• You will notice the number will show in the percentage format.

Hence, we can say that our visual code works effectively, and we are able to use the Format function in Excel.

3. Formatting for Logic Test

Now, we are going to write a VBA code to check the logic and format the cell value on that logic. The steps of this method are explained below:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

• As a result, a dialog box will appear.
• Next, in the Insert tab on that box, click the Module option.

• Then, write down the following visual code in that empty editor box.

Sub Format_Logic_Test()
Worksheets(13).Activate
  Cells(5, 2) = 2
  Cells(5, 3) = Format(Cells(5, 2), "Yes/No")
  Cells(6, 3) = Format(Cells(5, 2), "True/False")
  Cells(7, 3) = Format(Cells(5, 2), "On/Off")
  
  Cells(9, 2) = 0
  Cells(9, 3) = Format(Cells(9, 2), "Yes/No")
  Cells(10, 3) = Format(Cells(9, 2), "True/False")
  Cells(11, 3) = Format(Cells(9, 2), "On/Off")
End Sub

• Press ‘Ctrl+S’ to save the code.
• Now, close the Editor tab.
• Afterward, in the Developer tab, click on Macros from the Code group.

• A small dialog box entitled Macro will appear.
• Then, select the Format_Logic_Test option and click the Run button.

• You will get your desired result.

So, we can say that our visual code works precisely, and we are able to use the Format function in Excel.

4. Formatting Date

Here, we will format the date value of a cell through a VBA code. The steps of cell formatting are shown below:

📌 Steps:

• At first, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

• A small dialog box will appear.
• Now, in the Insert tab on that box, click the Module option.

• After that, write down the following visual code in that empty editor box.

Sub Format_Date()
Worksheets(14).Activate
  Cells(5, 2) = "Sep 3, 2003"
  Cells(5, 3) = Format(Cells(5, 2), "General Date")
  Cells(6, 3) = Format(Cells(5, 2), "Long Date")
  Cells(7, 3) = Format(Cells(5, 2), "Medium Date")
  Cells(8, 3) = Format(Cells(5, 2), "Short Date")
End Sub

• Then, press ‘Ctrl+S’ to save the code.
• Close the Editor tab.
• Next, in the Developer tab, click on Macros from the Code group.

• Another small dialog box titled Macro will appear.
• Now, select the Format_Date option.
• Finally, click on Run.

• You will get dates in multiple formats.

Therefore, we can say that our visual code works perfectly, and we are able to use the Format function in Excel.

5. Formatting Time Value

In the last example, we are going to write a VBA code to format our time value in multiple ways. The steps of formatting are described below:

📌 Steps:

• Firstly, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

• As a result, a dialog box will appear.
• Then, in the Insert tab on that box, click the Module option.

• Write down the following visual code in that empty editor box.

Sub Format_Time()
Worksheets(15).Activate
  Cells(5, 2) = "15:25"
  Cells(5, 3) = Format(Cells(5, 2), "Long Time")
  Cells(6, 3) = Format(Cells(5, 2), "Medium Time")
  Cells(7, 3) = Format(Cells(5, 2), "Short Time")
End Sub

• After that, press ‘Ctrl+S’ to save the code.
• Now, close the Editor tab.
• Next, in the Developer tab, click on Macros from the Code group.

• As a result, another small dialog box called Macro will appear.
• Select the Format_Time option and click the Run button.

• You will get the time value in different formats.

Finally, we can say that our visual code works perfectly, and we are able to use the Format function in Excel.

Read More: Excel Text Formula (TEXT Function)

💬 Things You Should Know

While you are going to use this function, you have to remember two things.

First, the TEXT function will be applicable only in the Excel spreadsheet. You cannot use this function in the VBA environment. Moreover, if you try to use the function in your VBA workspace, Excel will show you an error, and the code will not run ahead.

On the other hand, you can use the Format function only in the VBA workspace. Inside the Excel worksheet, you won’t be able to figure out any function called Format.

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to use the Format function in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!

Related Articles

• How to Show Grand Total in Pivot Table (3 Easy Methods)
• Use CODE Function in Excel (5 Examples)
• How to Use CHAR Function in Excel (6 Suitable Examples)
• Use FIXED Function in Excel (6 Suitable Examples)
• How to Auto Generate Number Sequence in Excel (9 Examples)