How to Use TEXT function in Excel

How to Use TEXT function in Excel

How to use TEXT Function in Excel

In Microsoft Excel, TEXT function is used to convert a number to text format in a given spreadsheet or in specific cells and display it in a specified format.

The TEXT function is a built in function of Mircosoft Excel that is categorized as String/TEXT function.It could be used as a worksheet function in Excel.

What it Returns
It returns text in the specified format.

Syntax:

=TEXT(value, format_text)

Input Arguments:
Value – the number that you want to convert into text or you may select any Reference cells.
format_text – the format in which you want to display the number.

Now let’s understand with an example:

Suppose you have one Data set as shown below in the image. Here are some random dates available in Column A for which you have to convert it into Text.

Dates

Formula to Convert : =Text(Value,Format Text)
For the Above Data Value is available in A2 Cell so Our Value reference should be A2. Format text should be in which format we want the text to be converted.

=TEXT(A2,”dddd”)

days dates by text

 

Here we have Format text as “dddd” so dates converted to days text.

This is a useful function when there is a need to show numbers in a format, or a need to combine numbers with text/symbols.

For example, if you have a value 2000 in cell A1. and you want to show the text – The revenue was 2,000, you can use the following formula: =” The revenue was “&TEXT(A1,”$#,###”)
The numbers are converted to text, and hence can not be used in calculations.
If you need to use these numbers in formulas/calculations, use custom number formatting.

Applied to :
Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.

Notes:

  • The value should be a numeric value
  • Format_text must appear in double quotation marks.
  • The TEXT function can be used with custom number formats