One of the most useful functions in Excel is the TEXT function. It allows you to format numbers, dates, and other values as strings, making them more readable or suitable for reports. If you’ve ever struggled with displaying numbers in a specific format without manually adjusting them, then this function is your best friend.
What Is the TEXT Function in Excel?
The TEXT function in Excel converts a number or a date to a string and formats it according to a specified format code. The general syntax for the function is:
=TEXT(value, format_text)Where:
- value – The numeric value, date, or time that you want to format.
- format_text – The format in which you want to display the value (enclosed in quotation marks).
Why Should You Use TEXT in Excel?
There are many reasons to use the TEXT function, including:
- Converting dates or numbers into a specific string format.
- Combining numbers with text in a cell without losing formatting.
- Creating custom formats for reporting.
Best TEXT Function Examples
Let’s go through some of the best applications of the TEXT function in Excel.
Formatting Numbers
Sometimes, you need a number to be formatted a certain way in text form. Here are some common examples:
| Formula | Output |
|---|---|
=TEXT(1234.56, "#,##0.00") |
1,234.56 |
=TEXT(0.785, "0%") |
79% |
=TEXT(12345, "00000") |
12345 |
Formatting Dates
When working with dates, Excel stores them as numbers but allows you to format them as text:
=TEXT(TODAY(), "MM/DD/YYYY")For example, if today’s date is June 15, 2024, this function returns:
06/15/2024Here are a few more date formatting examples:
=TEXT(TODAY(), "dddd, mmmm dd, yyyy")→ Saturday, June 15, 2024=TEXT(TODAY(), "dd-mm-yyyy")→ 15-06-2024
Combining TEXT with Other Functions
The TEXT function is particularly useful when combining it with other functions. One common use case is concatenation.
= "Today's date is " & TEXT(TODAY(), "MMMM dd, yyyy")This would return:
Today's date is June 15, 2024Adding Leading Zeros
Sometimes, you need numbers with leading zeros, such as ZIP codes or employee IDs. The default number format removes leading zeros, but TEXT keeps them:
=TEXT(42, "00000")This results in:
00042Formatting Time Values
Time values can also be formatted using TEXT:
=TEXT(NOW(), "hh:mm AM/PM")If the current time is 14:30, this formula will return:
02:30 PMHandling Currency Formats
When working with currency values, you can format them using the TEXT function:
=TEXT(1234.56, "$#,##0.00")→ $1,234.56=TEXT(7890, "€#,##0.00")→ €7,890.00
Limitations of the TEXT Function
While TEXT is incredibly useful, it has some drawbacks:
- It converts numbers into text, meaning calculations cannot be performed on the result.
- It may not recognize localized date or number settings in every version of Excel.
Final Thoughts
The TEXT function in Excel is an invaluable tool for formatting numbers, dates, and times into a readable text format. Whether you’re working on reports, dashboards, or financial statements, mastering TEXT will make your Excel skills even sharper.
Now that you’ve learned how TEXT works in Excel with the best TEXT examples, try applying it to your own spreadsheets. You’ll quickly see how powerful and flexible this function can be!
Other interesting article:
How PI works in Excel? Best PI examples