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

Lookup and Reference Examples

  • Basic INDEX MATCH approximate in Excel
  • How to use Excel TRANSPOSE Function
  • Two-way lookup with VLOOKUP in Excel
  • Perform case-sensitive Lookup in Excel
  • Extract all partial matches in Excel

Data Analysis Examples

  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Example of COUNTIFS with variable table column in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • How to calculate average last N values in a table in Excel

Data Validation Examples

  • Excel Data validation must not contain
  • Excel Data validation allow weekday only
  • Data validation must not exist in list
  • Excel Data validation with conditional list
  • Excel Data validation specific characters only

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 month 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 Julian format 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
  • Complete List of Excel Text Functions References and Examples
  • ASC function: Description, Usage, Syntax, Examples and Explanation
  • How to count specific words in a range in Excel
  • How to Capitalize first letter in a sentence in Excel
  • How to strip non-numeric characters in Excel
  • Calculate retirement date in Excel
  • How to get same date next year or previous year in Excel
  • Pad week numbers with zeros in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • Example of payment for annuity in Excel
  • Calculate periods for annuity in Excel
  • Calculate cumulative loan principal payments in Excel
  • Calculate payment periods for loan in Excel
  • Calculate payment for a loan in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles