If you want to about the TEXT and FORMAT functions in Excel, then you are in the right place. In this post, we will show you how you can use the TEXT and FORMAT functions with different examples so that you get a clear idea and understand the difference between these two functions.
There are many times when we have to format or change some data based on the spreadsheet. As a rule, we proceed as follows: In the event that when we right-click on a cell, the context options will appear. The Format Cells dialog box then can be accessed by selecting the Format Cells option from the context menu.
After that, we can see from the Format Cells dialog box that we can easily change the style of a number, currency, day, time, or percentage by selecting the relevant option.
There are, although, some other ways that can also be used to do the same. Here is a demonstration of two functions that may be used to format and change a variety of numbers, dates, times, and currency styles. The functions are the TEXT and the FORMAT functions. The TEXT function is a spreadsheet function that may be used anywhere in a sheet, but the Format function is only useable in VBA code and cannot be used in a spreadsheet.
So, keep reading this post until the end to learn more about these two functions.
Using The TEXT Function In Excel
The TEXT function in Excel outputs a number as text in the format specified by you. It is possible to transform numbers into text using the Excel TEXT Function, which can be found in most spreadsheet programs. A text string is created by converting a numeric number to a text string using this method. All versions of Excel have the TEXT formatting feature.
The main purpose of using this function is to convert an integer to text in a number formatting.
Syntax of the TEXT Function
The TEXT function has the following syntax:
=Text(Value, format_text)
Here:
Value: When we say VALUE, we’re talking about the numerical value that has to be converted to text.
Format_text: This is the format that we wish to use for the text of the document.
When is it necessary to use the Excel TEXT Function?
The TEXT function is used in the following situations:
- In situations when we need to automatically enter dates in a certain format.
- In situations when we need to present numbers in a certain format or in a more readable manner.
- In situations when we want to blend numbers with text or characters.
Now, let’s move on to the examples of how to use the TEXT function in Excel.
How To Format Number or Currency Using The TEXT Function
First, we’ll go through how to format a numeric value or a currency using the TEXT function in Excel. Suppose, there is a numerical value in cell C2 which is 1234567 and we want to extract only numbers from cells and customize it to appear as 1,234,567.00 or (1,234,567.00) or 1,234,567.
So, how can you do that? Is that even possible?
Yes, it is. You can easily format these numbers by using the TEXT function or you can use VBA to convert text format to number. As you can see in the screenshot below, we already did that in an instant. So, how do we do it? Follow the below steps carefully to do that:
- Open the spreadsheet and select a cell where you want to put the formatted number.
- Now, enter the following formula in that cell and hit the Enter button: =TEXT($C$2,”#,###.00″)
By using this formula, the number 1234567 will appear as 1,234,567.00. But if you have some other format styles in your mind, then take a look below and see if any of these format matches your desires:
(1,234,567.00) =TEXT($C$2,”(#,###.00)”)
-1,234,567.00 =TEXT($C$2,”-#,###.00″)
1,234,567 =TEXT($C$2,”#,###”)
1,234,567 =TEXT($C$2,”###,###”)
1234567.00 =TEXT($C$2,”####.00″)
1234567.00 =TEXT($C$2,”#.00″)
Not only we can format the numbers, but also we can format the numbers as a currency. Take a look below to know the formula:
$1,234,567.00 =TEXT($C$2,”$#,###.00″)
($1,234,567.00) =TEXT($C$2,”($#,###.00)”)
-$1,234,567.00 =TEXT($C$2,”-$#,###.00″)
￥1,234,567 =TEXT($C$2,”￥#,###”)
￥1,234,567 =TEXT($C$2,”￥###,###”)
$1234567.00 =TEXT($C$2,”$####.00″)
$1234567.00 =TEXT($C$2,”$#.00″)
This demonstrates how currency is shown. $ stands for the dollar, while ￥ stands for the Yuan, which would be the currency of China. Alternatively, you may use different currency symbols, as long as they are placed at the start of the formatting style.
How To Format Date or Time Using The TEXT Function
Now, we will see how to format the date or time by using the TEXT function. Suppose, we have a date value in cell G2 which is 4/24/2022 and we want to customize it to appear as Apr 24, 2022.
Follow the below steps to do that:
- Open the spreadsheet and select a cell where you want to put the formatted number.
- Now, enter the following formula in that cell and hit the Enter button: =TEXT($G$2,”MMM DD, YYYY”)
By using this formula, the date 4/24/2022 will appear as Apr 24, 2022. If you want, you can count dates in Excel too.
However, we gave some other format style’s formulas below. Let’s take a look:
24Apr2022 =TEXT($G$2,”DDMMMYYYY”)
24Apr22 =TEXT($G$2,”DDMMMYY”)
Apr 24, 2022 =TEXT($G$2,”MMM DD, YYYY”)
Sunday =TEXT($G$2,”DDDD”)
Sunday, 24Apr2022 =TEXT($G$2,”DDDD, DDMMMYYYY”)
Sunday, Apr 24, 2022 =TEXT($G$2,”DDDD, MMM DD, YYYY”)
04/24/2022 =TEXT($G$2,”MM/DD/YYYY”)
04/24 =TEXT($G$2,”MM/DD”)
2022-04-24 =TEXT($G$2,”YYYY-MM-DD”)
Also, we can format time, and on the other hand, we can combine time with a date! Isn’t that amazing? Choose your desired format style from below and copy the formula of it:
For time 10:28,
10:28 AM =TEXT($G$12,”H:MM AM/PM”)
10:28:00 AM =TEXT($G$12,”H:MM:SS AM/PM”)
4:32:33 PM =TEXT(NOW(),”H:MM:SS AM/PM”)
For time and date 10/25/2016 3:28:00 PM,
Oct 25, 2016 3:28:00 PM =TEXT($G$16,”MMM DD, YYYY H:MM:SS AM/PM”)
2016-10-25 3:28 PM =TEXT($G$16,”YYYY-MM-DD H:MM AM/PM”)
2016-10-25 15:28 =TEXT($G$16,”YYYY-MM-DD H:MM”)
So, in this way, you can format the date or time or combine them all together using the TEXT function.
How To Format Percentage, Fraction, or Mobile Number Using The TEXT Function
As you can see in the screenshot below, the TEXT function is also able to format fractions, percentages, mobile numbers, scientific numbers, and other numerical values.
For example, let’s suppose we have a numerical value in cell K2 which is 0.2854. And we want it to appear as a percentage like 29% or calculate the cumulative percentage of a dataset. So, can we do that?
Follow the steps below carefully to format numbers into percentages:
- Open the spreadsheet and select a cell where you want to put the formatted number.
- Now, enter the following formula in that cell and hit the Enter button: =TEXT($K$2,”0%”)
By using this formula, the numerical value 0.2854 will appear as 29% as you can see in the image below.
Also, below we were given some other format styles and formulas from which you can choose whichever you want:
28.5% =TEXT($K$2,”0.0%”)
28.54% =TEXT($K$2,”0.00%”)
For fraction value 4.640381685,
14/3 =TEXT($K$6,”?/?”)
413/89 =TEXT($K$6,”?/??”)
3884/837 =TEXT($K$6,”?/???”)
9/2 =TEXT($K$6,”?/2″)
19/4 =TEXT($K$6,”?/4″)
37/8 =TEXT($K$6,”?/8″)
74/16 =TEXT($K$6,”?/16″)
46/10 =TEXT($K$6,”?/10″)
464/100 =TEXT($K$6,”?/100″)
For mobile number 8613894425469,
(86) 138-944-25469 =TEXT($K$13,”(##) ###-###-#####”)
So, in this way, you can format percentages, fractions, or mobile numbers using the TEXT function.
Using The Format Function In Excel
Excel has a built-in function known as the FORMAT function, which is classified as a String/Text Function in its documentation. It is possible to utilize it as a VBA function (VBA) in Microsoft Excel. This function is available as a VBA function, which means that it may be used in macro code that is inputted using the Microsoft Visual Basic Editor.
But, for utilizing the FORMAT Function as a macro, you have to enable Excel’s VBA.
Note:: Keep in mind that the FORMAT procedure should not be used in the spreadsheet and that you must always input it into the Visual Basic Editor when utilizing it.
Syntax of the FORMAT Function (VBA)
The FORMAT function has the following syntax:
Format ( expression, [ format ] ).
Here:
expression is the string value that we are going to format.
Format: It is the format option that will apply to the value in the expression. Optional. Specifically, it is the format that should be used with the phrase. The format can be customized, or one of the listed formats Excel has provided, such as the ones shown below:
| Format | Explanation |
| General Number | Shows an integer without thousand separators. |
| Currency | Shows a thousand separators and two decimal places. |
| Fixed | Has two digits in front of and two digits after the decimal place. Displayed at least one digit. |
| Standard | When it comes to decimal places and a thousand separators, this function shows at least one digit on each side of it. |
| Percent | Percentage values are shown as a number multiplied by 100 and accompanied by the percent symbol. Right after the decimal point, it shows an additional two numbers. |
| Scientific | It shows scientific notation. |
| Yes/No | Displays No if the value is a zero. Yes is shown if the number is not zero. |
| True/False | If the value is zero, it will show False. For non-zero numbers, it displays True. |
| On/Off | Off when the value is zero.
When it says “On,” it’s not a zero. |
| General Date | The date displayed depends on the settings of your computer. |
| Long Date | Using your system’s long date option, it displays the current date in your calendar. |
| Medium Date | The date is shown using the medium date setting on your computer. |
| Short Date | Displays the current date depending on the short date setting on your system. |
| Long Time | Using your system’s long time option, it shows the current time. |
| Medium Time | Based on your system’s medium time setting, this function shows the current time. |
| Short Time | Time is shown in a short time format according to the short time setting on your system. |
Example of a Format function:
In the below image, you can see an example of the format function and its result of it.
So, in this way, you can easily use the TEXT and FORMAT functions in Excel.
Conclusion
Using the TEXT and FORMAT functions is very handy, especially when it comes to formatting a cell value. In this post, we tried to show you some of the best ways to use TEXT and FORMAT functions in Excel with several examples of it. We hope you found this article helpful and that it helped you know something.
