The TEXT function in Excel allows you to convert numerical values into text strings with a specified format. For instance, transforming a date from '15/03/2020' to 'March 15, 2020' can be achieved using this function.

This article provides a comprehensive guide on using the TEXT function in Excel, complete with formulas and examples.

Understanding the Syntax

The basic syntax for the TEXT function is:

=TEXT(value, format_text)

The function requires two arguments:

  • value: The numeric value you wish to convert into text. This could be a direct number, date, or a cell reference containing a numeric value.
  • format_text: The format code that defines how the value should be displayed. This code must be enclosed in double quotation marks.

Common Format Codes for the TEXT Function

While the first argument is straightforward, selecting the correct format code is crucial to get the desired output. Below are some commonly used format codes:

Format Code Description Example
0 Displays digits without decimal places.

#.0 — Always shows one decimal place.

For example, 5.50 becomes 5.5.
# Displays digits without unnecessary zeros.

#.## — Shows up to two decimal places.

For example, 3.777 becomes 3.78.
? Aligns numbers by decimal point in a column.

#.? — Displays one decimal place, aligning the decimal points.
. Represents a decimal point.
, Thousands separator.

###,### — Adds a thousands separator.

For example, 195200 becomes 195,200.
0% Displays numbers as percentages.

For example, 0.285 becomes 28.5%.

You can also include the following symbols in your format codes, which will appear exactly as entered:

Symbol Description
+ and - Plus and minus signs
( and ) Parentheses
: Colon
^ Caret
' Apostrophe
{ and } Curly brackets
< and > Less-than and greater-than signs
= Equal sign
/ Forward slash
! Exclamation point
& Ampersand
~ Tilde
Space character

Format Codes for Dates and Times

When dealing with dates and times, specific format codes are used to display them appropriately. Here are some of the most commonly used date and time format codes:

Format Code Description and Examples
d Day of the month without a leading zero (e.g., 1 to 31).
dd Day of the month with a leading zero (e.g., 01 to 31).
ddd Abbreviated weekday (e.g., Mon to Sun).
dddd Full name of the weekday (e.g., Monday, Tuesday).
m Month number without a leading zero (e.g., 1 to 12).
mm Month number with a leading zero (e.g., 01 to 12).
mmm Abbreviated month name (e.g., Jan, Feb).
mmmm Full month name (e.g., January, February).
yy Two-digit year (e.g., 21 for 2021).
yyyy Four-digit year (e.g., 2021).
h Hour without a leading zero (e.g., 1 to 12).
hh Hour with a leading zero (e.g., 01 to 12).
m Minutes without a leading zero (e.g., 1 to 59).
mm Minutes with a leading zero (e.g., 01 to 59).
s Seconds without a leading zero (e.g., 1 to 59).
ss Seconds with a leading zero (e.g., 01 to 59).
AM/PM Displays time in 12-hour format followed by AM or PM.

Utilizing the TEXT Function in Excel

Now that we've covered the syntax and format codes, let's explore practical examples of using the TEXT function in Excel.

Example 1: Displaying a Whole Number

Suppose you have a number in cell A1 and want to display it as a whole number without decimal places. Use the following formula:

=TEXT(A1, "0")

Example 2: Displaying a Single Decimal Place

To format a number to show one decimal place, use:

=TEXT(A1, "0.0")

Below is a table with various TEXT function formulas applying different formats to numeric values. You can try these formulas in your spreadsheet:

Value Formula Formatted Value
4963.34 =TEXT(A2, "0.000") 4963.340
5300.52 =TEXT(A3, "#,##0") 5,301
5.12 =TEXT(A4, "# ?/?") 5 1/8
0.4963 =TEXT(A5, "#%") 50%
9600.60 =TEXT(A6, "$#,##0.0") $9,600.6
20 =TEXT(A7, "~#!") ~20!
5656 =TEXT(A8, "00000000") 00005656

These formulas have been applied in column C of the spreadsheet as shown below:

Combining TEXT Function with Other Formulas

The TEXT function can be integrated with other formulas to perform calculations and display results in a specified format.

Example: Calculating Net Profit

Assume you have the Gross amount in cell C6 and Expenses in cell C7. To calculate the Net Profit and display it with a descriptive text, use:

= "Your Net Profit is " & TEXT(C6 - C7, "$#,###.00")

This formula calculates the net profit by subtracting Expenses from Gross and then formats the result as currency, appending it to the string "Your Net Profit is".

Formatting Mobile Numbers

When entering long numbers like mobile numbers, Excel might convert them into scientific notation. To display these numbers correctly, the TEXT function can be used.

Consider mobile numbers that have been turned into scientific notation:

To format these numbers back into readable mobile numbers, use the formula:

=TEXT(A1, "+############")

The format code "+############" ensures the number is displayed correctly.

To separate the country code from the rest of the number for better readability, modify the format code:

=TEXT(A1, "+##-##########")

Formatting Dates with the TEXT Function

Excel stores dates as serial numbers, which can be confusing when you want to display them in a readable format. The TEXT function can convert these serial numbers into formatted date strings.

Example: Changing Date Format

If you have a date in cell A1 (e.g., 05-03-2015) and want to display it as 'Mar 5, 2015', use:

=TEXT(A1, "mmm d, yyyy")
  • mmm: Abbreviated month name
  • d: Day of the month
  • yyyy: Four-digit year

You can apply various date formats using different format codes. Here's how the same date can be displayed in multiple formats:

Combining Dates and Text

When you want to concatenate text with dates, using the TEXT function ensures the date is displayed correctly.

Example: Merging Name and Date of Birth

If you have names in column A and their dates of birth in column B, directly concatenating them might result in the date appearing as a serial number.

To display the date properly, use the TEXT function along with the CONCAT function:

=CONCAT(A2, " - ", TEXT(B2, "dd/m/yy"))

This will present the date in the format 'dd/m/yy'.

You can enhance the output by adding more descriptive text and changing the date format:

Copy the formula to other cells using the fill handle to apply it to multiple rows:

Example: Displaying the Current Date

You can use the =TODAY() function to get the current date and combine it with text:

By mastering the TEXT function, you can control how numeric and date values appear in your Excel spreadsheets, making your data more readable and professional.