TEXT Function in Excel – How to use it

In this tutorial let us explore how to use TEXT Function in Excel with examples

Summary

TEXT function converts a number into a desired text format. It could add a currency symbol to a number or convert a valid date to a desired date format like Dec 10, 2019 or September 05, 2015. We could use this to create dynamic content like chart title or paragraph heading.

The function is an inbuilt worksheet function in excel which comes under the String/text category. This function could be used as a single function or nest it into other functions. This function could also be used in a macro (Excel VBA)

Syntax:

The syntax for the function is

=TEXT (value, format text)

Arguments or Inputs

The function requires the following arguments

value:

This argument is required

This is the number or date to be converted to text

format_text :

This argument is required

This is the text format to be displayed

Result:

The function will convert a number or a date to a desired text format.

Example

The following screenshot shows how the TEXT function is used with different input values and the result of the function.

Explanation based on the above screenshot

Function is TEXT(A2,B2)

A2 is the reference to the value in cell A2 which is a valid date.

B2 is the reference to the format text shown in cell B2.

Either we could use this function like shown in column C or refer to a cell which has the desired format like in column B

Result is Wednesday 22, 2020.

Function is TEXT(A3,B3)

A3 is the reference to the value in cell A3 in this case a valid number representing a date. In Excel dates are stored as number.

B3 is the reference to the format text shown in cell B3.

Result is May 18, 2018.

Function is TEXT(A4,B4)

A4 is the reference to the value in cell A4 in this case a valid date stored as number. remember Excel stored dates as number.

B4 is the reference to the format text shown in cell B4.

Result is 48239

Function is TEXT(A5,B5)

A5 is the reference to the value in cell A5 in this case a decimal value.

B5 is the reference to the format text shown in cell B5 which is a percentage format.

Result is 50%

The text function could be used to create dynamic content like the below