Format Cell using EXCEL function

In EXCEL, we can format the numbers into different formats using the Format option. In some instances, we need to format the cell value which is a result of another formula. In other output from EXCEL format should be in the desired format. There is a EXCEL function called TEXT. This is useful for this. In this post we will learn the way to format cell using a EXCEL function TEXT.

Format Cell Values using EXCEL Function

We can use TEXT excel function to Format Cell in Excel. This method is useful when we need to implement formatting inside a EXCEl formula.

Syntax of Excel TEXT Function

=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 excel formula. The biggest advantage of this excel function 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.