Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Conditional Formatting Rules in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • How to calculate average last N values in a table in Excel

References

  • How to use Excel MMULT Function
  • Count rows that contain specific values in Excel
  • Two-column Lookup in Excel
  • How to use Excel COLUMN Function
  • How to retrieve first match between two ranges in Excel

Data Validations

  • Excel Data validation allow uppercase only
  • Excel Data validation must contain specific text
  • Excel Data validation unique values only
  • How To Create Drop-down List in Excel
  • Excel Data validation date in next 30 days

TEXT function: Description, Usage, Syntax, Examples and Explanation

by

What is TEXT function in Excel?

TEXT function is one of Microsoft Office Excel TEXT functions that lets you change the way a number appears by applying formatting to it with format codes. It’s useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols.

Note: The TEXT function will convert numbers to text, which may make it difficult to reference in later calculations. It’s best to keep your original value in one cell, then use the TEXT function in another cell. Then, if you need to build other formulas, always reference the original value and not the TEXT function result.

Syntax of TEXT function

In its simplest form, the TEXT function says:

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

Examples of TEXT function

Steps to follow:

1. Open a new Excel worksheet.

2. Copy data in the following table below and paste it in cell A1

Note: For formulas to show results, select them, press F2 key on your keyboard and then press Enter.

You can adjust the column widths to see all the data, if need be.

Take Note of the format codes within quotation marks.

Formula Description
=TEXT(TODAY(),”MM/DD/YY”) Today’s date in MM/DD/YY format, like 03/14/12
=TEXT(TODAY(),”DDDD”) Today’s day of the week, like Monday
=TEXT(NOW(),”H:MM AM/PM”) Current time, like 1:29 PM
=TEXT(1234.567,”$#,##0.00″) Currency with a thousands separator and 2 decimals, like $1,234.57. Note that Excel rounds the value to 2 decimal places.
=TEXT(0.285,”0.0%”) Percentage, like 28.5%
=TEXT(4.34 ,”# ?/?”) Fraction, like 4 1/3
=TRIM(TEXT(0.34,”# ?/?”)) Fraction, like 1/3. Note this uses the TRIM function to remove the leading space with a decimal value.
=TEXT(12200000,”0.00E+00″) Scientific notation, like 1.22E+07
=TEXT(1234,”0000000″) Add leading zeros (0), like 0001234
=TEXT(1234567898,”[<=9999999]###-####;(###) ###-####”) Special (Phone number), like (123) 456-7898
=TEXT(123456,”##0° 00′ 00””) Custom – Latitude/Longitude

Post navigation

Previous Post:

DCOUNT function: Description, Usage, Syntax, Examples and Explanation

Next Post:

231 Keyboard Shortcut Keys In Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IF with boolean logic in Excel
  • How to use Excel TRUE Function
  • How to use Excel OR Function
  • How to use Excel AND Function
  • How to return blank in place of #DIV/0! error in Excel

Date Time

  • Convert decimal seconds to Excel time
  • Convert text to date in Excel
  • Display the current date in Excel
  • MONTH function: Description, Usage, Syntax, Examples and Explanation
  • Check If Two Dates are same month in Excel

Grouping

  • Group times into unequal buckets in Excel
  • Map text to numbers in Excel
  • If cell contains one of many things in Excel
  • Map inputs to arbitrary values in Excel
  • Group times into 3 hour buckets in Excel

General

  • Zoom Worksheet in Excel
  • Count cells less than in Excel
  • How to generate random number weighted probability in Excel
  • How to generate random number between two numbers in Excel
  • Hide and Unhide Columns or Rows in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning