TEXT function in Excel

TEXT function in Excel

The TEXT function allows change the way a number appears by applying formatting to it with format codes.

Syntax:

=TEXT(Value you want to format, “Format code you want to apply”)

figure 1.0

Format codes:

  • “$#,##0.00” : Currency with a thousands separator and 2 decimals, like $2,374.43. Note that Excel rounds the value to 2 decimal places.
  • “MM/DD/YY” : Today’s date in MM/DD/YY format, like 07/08/20
  • “DDDD” : Today’s day of the week, like Monday
  • “H:MM AM/PM” : Current time, like 10:39 AM
  • “0.0%” : Percentage value like 11.2%
  • “# ?/?” : Fraction, like 2 2/5
  • “# ?/?” : Fraction, like 2/3. Note this uses the TRIM function to remove the leading space with a decimal value.
  • “0.00E+00” : Scientific notation, like 1.34E+08
  • “[<=9999999]###-####;(###) ###-####” : Special (Phone number), like (121) 456-7898
  • “0000000” : Add leading zeros (0), like 0001234
  • “##0° 00′ 00”” : Custom – Latitude/Longitude

There are many formats can be built by exploring Format Cells dialog where you can build format as per need and copy paste into TEXT function.

figure 1.1

Leave a Reply

Your email address will not be published.