Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel TRANSPOSE Function
  • Approximate match with multiple criteria in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Get nth match with INDEX / MATCH in Excel
  • Count rows that contain specific values in Excel

Data Analysis Examples

  • Reverse List in Excel
  • How to Create Area Chart in Excel
  • Data Series in Excel
  • Chart Axes in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel

Data Validation Examples

  • Excel Data validation date in specific year
  • Excel Data validation no punctuation
  • Excel Data validation must contain specific text
  • Excel Data validation specific characters only
  • Excel Data validation exists in list

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.

Worked Example:   How to join date and text together in Excel

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

Worked Example:   Get day name from date in Excel

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
Worked Example:   Convert date to text in Excel

Post navigation

Previous Post:

NETWORKDAYS.INTL function: Description, Usage, Syntax, Examples and Explanation

Next Post:

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

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
  • Remove last characters from right in a cell in Excel
  • How to create email address with name and domain in Excel
  • SUBSTITUTE function: Description, Usage, Syntax, Examples and Explanation
  • How to check if cell contains some words but not others in Excel
  • How to strip numeric characters from cell in Excel
  • Convert date to month and year in Excel
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation
  • Get week number from date in Excel
  • Convert Unix time stamp to Excel date
  • How to get Weekdays, Working days between Two Dates in Excel
  • How to calculate annuity for interest rate in excel
  • TBILLYIELD function: Description, Usage, Syntax, Examples and Explanation
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • ODDFPRICE function: Description, Usage, Syntax, Examples and Explanation
  • PV function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles