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
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.
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.
Hope this helps to create your excel templates by using the Formula to Format Cell.