TEXT Excel Formula

Author Zaheer    Category Formulas     Tags ,

Microsoft Excel is one of the well known computer software that was created by the Microsoft Company. It provides lots of advantages especially to students and even the professionals in various fields. Excel features numerous functions that can be useful particularly when the user is working with mathematical and financial calculations. Most people use Microsoft Excel just for its basic functions although it contains a broad range of complex functions which can be employed by the experts in their respective fields. One of the functions that are contained in Excel is the so-called TEXT Function. This article provides you fundamental concepts and examples that will assist you in understand how the TEXT function works. If you want to learn more, read on.

Most probably, Excel users what to display a value in the Excel spreadsheet with their desired format. Obviously, the easiest way to do that is to employ the Format Cells dialog box to alter the format of the cell’s display.

The TEXT function in Microsoft Excel permits the users to enter a function in a cell that returns a result which is converted into text. For example, you cam utilize this function to change a numerical value to currency or to alter the way a date is shown. It is also possible to make a sentence that contains the values. However, the TEXT function only works if the display remains the same.

Overview of the TEXT function

The TEXT function in Excel is utilized to convert a number value into text. The result is formatted depending on the format that was specified by the user. The formula for the TEXT function is:

=TEXT(value, format_text)

The function arguments are as follows:

  • Value – this argument is the numeric value that you wish to convert into text.
  • Format_text – this parameter specifies the format that you want to apply to the numerical value.

This article will provide you the format definitions that can be utilized in the TEXT function. These definitions mean the same when utilized in the traditional style of Excel Cell Formatting.

  • 0 – it maintains the display of a digit in its position.
  • # – it displays the digit if it can increase the accurateness of the numerical value (however, it doesn’t show the digit if there is a zero at the end of the decimal).
  • .  – It specifies the position that will be taken by the decimal
  • s – Second

s – It specifies the one or two digit form of second (i.e. 3, 47)

s – It specifies the two digit form of second (i.e. 03, 47)

  • m – Minute

m – It specifies the one or two digit of minute (i.e. 2, 56)

m – It specifies the two digit form of minute (i.e. 02, 56)

  • h – Hour

h – it specifies the one or two digit form of hour (i.e. 2, 21)

h – it specifies the two digit form of hour (i.e.02, 21)

  • d – Day of the week or month

dddd – complete name of the day (i.e. Wednesday, Thursday)

ddd – shortened or abbreviated name of the day (i.e. Wed, Thurs)

dd  – It specifies the two digit form (i.e. 02, 13)

d  – It specifies the one or two digit form (i.e. 2, 13)

  • y – Year

yyyy – it specifies the four digit form of the year (i.e. 2000, 2011)

yy – it specifies the two digit form of the year (i.e. 98, 07)

  • AM/PM – it specifies that a time should be displayed using the 12-hour format of the clock, then followed by AM or PM.

For a better understanding on how to use the TEXT function in Microsoft Excel, here is an example.

Commonly, the TEXT function is used to insert dates into text values. As an example, “Nick Peterson” is contained in cell A3 and “11/02/1977” is contained in cell B3. You can click on C3 and type this formula on the formula bar: =A3 & “ “ & TEXT(B3, “dd/m/yy”). The result that will be displayed in cell C3 is: “Nick Peterson 11/2/77”.

Excel’s TEXT Function Uses

You can also apply this function in these two cases:

  • You want to alter the cell’s display at run time (as an example, you want to show greater values which doesn’t include the function).
  • You want to add text to the content of a cell (as an example, “$3.23 discount” rather than “$3.23”)

Post comment