Formula to Format Cell in Excel Tips

In excel, we can format the numbers into different formats using the Format option. But sometimes we need to format the number which is a result of another formula. To do this, we can use TEXT formula.  Using this you can format date, time, numbers currency etc. So in this tutorial, we will see how to use Formula to Format Cell Values in Excel.

Excel Formula to Format Cell Values

Formula to Format Cell in Excel used is TEXT. This is very useful function when we need to implement this in a complex formulas.

Syntax of Excel TEXT Formula

=TEXT(value, format_text)
where
Value is the number which we want to change the format.
format_text is the required format.
Examples of converting the format of values in Excel using formula
We have a value 41479 in cell A1.
=TEXT(A1,”dd-mmm-yyyy”)
The above formula changes the format of the value in cell A1 to dd-mmm-yyyy format(24-Jul-2013).
Similarly you can use any format in which you want to display the number. Below are some more example for the same.
=TEXT(A1,”#,##0″) this will give output as 41,479.
=TEXT(A1,”£#,##0;-£#,##0″) which gives output in the format £41,479.
Like this you can use your own formatting using this formula. The biggest advantage of this excel formula is that you can use this formula inside another formula or function to format the output. For example you want to concatenate date and a text . In Cell A1 we have “Today Date is” and in cell B1 we have 24-Jul-2013. Now if we use normal concatenation.
=A1&B1 the output will look like this Today Date is41479. So we need to use the text formula to convert the number in cell B1 to Date and then we can concatenate. The modified formula look like this.
=A8&TEXT(B8,”dd:mmm:yyyy”) which shows the output as Today Date is24:Jul:2013.
You can even use this formula with other excel formulas such as vlookup, hlookup. The formula to change the format of vlookup formula out is as mentioned below.
=TEXT(VLOOKUP(B15,A13:B13,2,FALSE),”DD:MMM:YYYY”)
Hope this helps to create your excel templates by using the Formula to Format Cell.

0 comments… add one

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.