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.


📚 Note:

All the operations of this article are accomplished by using the Microsoft Office 365 application.


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:

=TEXT($B$5,"#,###.00")

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

=TEXT($B$5,"(#,###.00)")

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

=TEXT($B$5,"-#,###.00")

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

=TEXT($B$5,"#,###")

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

=TEXT($B$5,"###,###")

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

=TEXT($B$5,"####.00")

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

=TEXT($B$5,"#.00")

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

Formatting Number Value to Format function in Excel

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


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:

=TEXT($B$5,"$#,###.00")

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

=TEXT($B$5,"($#,###.00)")

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

=TEXT($B$5,"-$#,###.00")

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

=TEXT($B$5,"¥#,###")

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

=TEXT($B$5,"¥###,###")

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

=TEXT($B$5,"$####.00")

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

=TEXT($B$5,"$#.00")

Press Enter. You will get your desired currency formatting.

Formatting Currency using VBA Format function

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:

=TEXT($B$5,"DDMMMYYY")

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

=TEXT($B$5,"DDMMMYY")

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

=TEXT($B$5,"MMM DD, YYYY")

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

=TEXT($B$5,"DDDD")

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

=TEXT($B$5,"DDDD, DDMMMYYYY")

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

=TEXT($B$5,"DDDD, MMM DD, YYYY")

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

=TEXT($B$5,"MM/DD/YYYY")

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

=TEXT($B$5,"MM/DD")

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

=TEXT($B$5,"YYYY-MM-DD")

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

Formatting Date with TEXT function

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:

=TEXT($B$5,"H:MM AM/PM")

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

=TEXT($B$5,"H:MM:SS AM/PM")

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

=TEXT(NOW(),"H:MM:SS AM/PM")

We will also use the NOW function.

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

Formatting Time using TEXT function in Excel

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:

=TEXT($B$5,"MMM DD, YYYY H:MM:SS AM/PM")

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

=TEXT($B$5,"YYYY-MM-DD H:MM AM/PM")

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

=TEXT($B$5,"YYYY-MM-DD H:MM")

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

Formatting Combined Date and Time using the TEXT function to format

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:

=TEXT($B$5,"0%")

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

=TEXT($B$5,"0.0%")

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

=TEXT($B$5,"0.00%")

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

Formatting Percentage by the TEXT function to format

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


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:

=TEXT($B$5,"?/?")

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

=TEXT($B$5,"?/??")

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

=TEXT($B$5,"?/???")

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

=TEXT($B$5,"?/2")

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

=TEXT($B$5,"?/4")

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

=TEXT($B$5,"?/8")

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

=TEXT($B$5,"?/16")

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

=TEXT($B$5,"?/10")

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

=TEXT($B$5,"?/100")

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

Formatting Fraction Number through TEXT function to format

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:

=TEXT($B$5,"0.00E+00")

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

=TEXT($B$5,"0.0E+00")

Press Enter. You will get the formatted value.

Formatting Scientific Number by the TEXT function to format

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:

=TEXT($B$5,"(##) ###-###-#####")

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

Formatting Telephone Number with TEXT function to format

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:

=TEXT($B$5,"00")

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

=TEXT($B$5,"000")

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

=TEXT($B$5,"0000")

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

Formatting Zero Leading Number by the TEXT function

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:

Format(Expression, [Format])

⏺ Arguments Explanation:

Argument Required/Optional Explanation
Expression Required Expression is that text string or cell value that we want to format according to our needs.
Format Optional This is our desired cell formatting.

⏺ 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.

Opening VBA Editor to write a code

  • 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.

Launching Macros to Run the VBA Code

  • 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.

Selecting Format_Number Code to Format Number in Excel

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

Format number using VBA Format function

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.

Opening VBA Editor to write a code

  • 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.

Launching Macros to Run the VBA Code

  • 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.

Selecting Format_Percentage option to format number into percentage by VBA Format function

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

Formatting number into percentage by VBA Format function

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.

Opening VBA Editor to write a code

  • 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.

Launching Macros to Run the VBA Code

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

SelectingFormat_Logic_Test option to format according to logic by VBA Format function

  • You will get your desired result.

Formatting logic by Excel VBA Format function

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.

Opening VBA Editor to write a code

  • 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.

Launching Macros to Run the VBA Code

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

Selecting Format_Date option to format date by VBA Format function

  • You will get dates in multiple formats.

Formatting date by Excel VBA Format function

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.

Opening VBA Editor to write a code

  • 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.

Launching Macros to Run the VBA Code

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

Selecting Format_Time option ti change format of time by VBA Format function

  • You will get the time value in different formats.

Formatting time value by VBA Format function

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

Zhiping Yan

Zhiping Yan

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

10 Comments
  1. Reply
    Roger Gruenenfelder Dec 14, 2016 at 7:31 AM

    Good day. Is it right that once I put a number or date in a Text format, I am unable to calculate with it ?

  2. Reply
    Roger Gruenenfelder Dec 15, 2016 at 11:29 PM

    Thanks. I have another question.
    Let’s say I have a problem within an existing sheet or workbook and try to ask you for a solution by texting you (f.e. in this comment space). This might be unconvincing for you, because, either I don’t know how to formulate my problem or you can’t see the building of my sheet/formulas etc.
    Is there a way to send you example files, where you can go through and see where I made a ridiculous mistake and you have the power to help me with your immense knowledge ?

  3. Reply
    Roger Gruenenfelder Dec 15, 2016 at 11:35 PM

    WHOW. I see you reacted to my former post for the possibility to download your posts as PDF for later evaluation.
    Great job.
    Thanks a lot for this unique opportunity.
    I like your posts very much, compared to other, similar newsletters, this is the easiest to handle.
    Congrats from here…

  4. Nice! You solved my question in seconds after googling a question about how to format a cell value. Thank you for the clear examples!

    • Welcome WILLIAM, it’s always a pleasure to hear that we were of any help to you.
      Thanks for the nice comment. Good luck.

  5. Yup, me, too! You gave me the concise use for TEXT. Thank you.

Leave a reply

ExcelDemy
Logo