Introduction

Excel is a powerful application that simplifies data manipulation and formatting. The TEXT function is a valuable resource for customizing text, dates, and numbers to suit individual preferences. With many formatting possibilities, the TEXT function allows users to modify data appearance effortlessly. In this comprehensive article, we will explore the full scope of Excel’s TEXT function and provide various text formulas in Excel examples to help you master its capabilities. From basic formatting to advanced text manipulations, this guide will equip you with the knowledge to wield the TEXT function effectively and enhance your Excel skills.

Text Formula in Excel

Excel’s TEXT function can format text, dates, and numbers similarly. The two inputs it accepts are the value you wish to format and the code defining the format you want. The format code consists of special characters that represent various formatting options.

Excel TEXT function – Syntax

The Excel TEXT function’s fundamental application entails providing a value and a format code to indicate the desired format. For instance, you can use the formula below to show a date in cell A1 as “dd-mmm-yyyy,” for example:

=TEXT(A1, "dd-mmm-yyyy")

This text formula in excel will convert the date value into the desired format. The TEXT function can be combined with other functions, such as CONCATENATE, to create more complex formatting scenarios.

Format Codes Used in the TEXT Function

Excel TEXT function’s syntax is straightforward, but providing the right format code can be a bit tricky. The TEXT function accepts most format codes commonly used in Excel number formats. Below is a table listing some of the most common and frequently used format codes:

The format codes used in the TEXT function determine how the value will be displayed. 

Some Common Format Codes

• “dd” stands for the day of the month, for example, 1, 2, or 3.
• “mmm” indicates the month’s name (e.g., Jan, Feb, Mar).
• “yyyy” for a year with four digits (such as 2023)
• “hh” signifies the hours (for example, 01, 02, or 03).
• Minutes are indicated by “mm” (00, 01, or 02).
• “ss” stands for the second, as in 00, 01, or 02

Special Characters in Format Codes

You can include any of the following characters in the format code, and they will be displayed exactly as entered:

Just a few samples of format codes are shown here. Excel provides a wide range of format codes to suit different formatting needs.

How to Use the TEXT Function in Excel?

The TEXT function in Excel is a powerful tool that allows you to manipulate and format data in custom ways. Here are three common use cases for the TEXT function:

Concatenate Text and Number or Date in a Custom Format:

Using Excel’s TEXT function, you can build a unique format by combining text with numbers or dates. This is helpful when you wish to present information in a particular manner. Use this format to concatenate text with a number or date:

=TEXT(value, "format")

For example, Suppose cell A1 has the phrase “Product Code:” and cell B1 contains the product code as a numeric value. You can concatenate them by using the TEXT function in the manner shown below:

=TEXT(B1, "Product Code: ")

This text formula in excel will display “Product Code: ” followed by the value in cell B1.

Add Leading Zeros to Numbers

Adding leading zeros to numbers can be crucial, especially when dealing with codes or identification numbers that require a specific format. The TEXT function allows adding leading zeros using a custom format code. Here’s the format:

=TEXT(value, "00000")

For example, suppose you wish to present a list of numbers in column A with five leading zeros. The TEXT function can be utilized as follows:

=TEXT(A1, "00000")

This formula will add leading zeros to the number in cell A1.

Convert Values to Phone Numbers in a Specific Format

Using the TEXT function, you can transform values into a particular phone number format. When you wish to show phone numbers with brackets, dashes, or other formatting choices yet save them as basic numbers, this is useful. Consider the following as a fictitious example:

=TEXT(value, "(000) 000-0000")

In column A, imagine you have a list of phone numbers you wish to format as “(XXX) XXX-XXXX.” You can do the following with the TEXT function:

=TEXT(A1, "(000) 000-0000")

This formula will convert the phone number in cell A1 into the desired format.

The TEXT function in Excel offers tremendous flexibility when customizing the data display. You can add leading zeros, concatenate text and dates/numbers, and convert values to particular formats by using it efficiently. These are a few ways the TEXT function can be used in practice. Try utilizing different format codes to attain the desired outcomes and improve your data’s visual appeal.

Examples of TEXT Function

The TEXT function in Excel offers many possibilities for formatting data. Let’s explore some common examples and use cases of the TEXT function, categorized into formatting dates and times, formatting numbers, and manipulating text strings.

Formatting Dates and Times

1. Converting Date Formats

The TEXT function is commonly used to convert dates into different formats. Let’s say you wish to display a date in cell A1 that is formatted “yyyy-mm-dd” as “dd/mm/yyyy.” Apply the following formula:

=TEXT(A1, "dd/mm/yyyy")

This formula will convert the date into the desired format.

2. Extracting Specific Components (Day, Month, Year, etc.)

The TEXT function allows you to extract specific components of a date. In cell A1, imagine you have a date you wish to extract the day. The following text formula in excel can be used:

=TEXT(A1, "dd")

Similarly, you can extract the month using the following:

=TEXT(A1, "mm")

And you can extract the year using the following:

=TEXT(A1, "yyyy")

These formulas will extract the respective components from the date.

3. Formatting Time Values

The TEXT function can also be used to format time values. Think of a scenario where you want to display a time value in cell A1 in the format “hh:mm AM/PM”. You can apply the formula below:

=TEXT(A1, "hh:mm AM/PM")

This formula will convert the time into the desired format.

Formatting Numbers

1. Applying Custom Number Formats

The TEXT function is useful for applying custom number formats to numeric values. Let’s assume you want to use the code below to get the time from a date in cell A1. Say you want cell A1 to show a number with two decimal places. You can use the following formula:

=TEXT(A1, "0.00")

This formula will format the number with two decimal places.

2. Adding Currency Symbols

The TEXT function can display a number with a currency sign. Suppose you want to add a dollar sign to a number in cell A1 representing a monetary value. The formula below can be used:

=TEXT(A1, "$0.00")

This formula will add the dollar sign to the number.

3. Displaying Decimal Places and Thousands of Separators

Using the TEXT function, you can choose the number of thousands of separators and decimal places. Let’s say you want cell A1 to show a number with two decimal places and 1,000 separators. The following formula can be used:

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

This formula will format the number with two decimal places and a thousand separators.

Manipulating Text Strings

1. Concatenating Text with Numbers and Dates

The TEXT function allows text, including dates and numbers, to be merged. Let’s imagine that you want to mix a text value from cell A1 with a number from cell B1 in a hypothetical scenario. The following formula can be used:

=TEXT(B1, "0") & " " & A1

This formula will concatenate the number with the text, separated by a space.

2. Extracting Substrings

The TEXT function can be used to extract substrings from a text string. Cell A1 contains a text value you wish to get the first three letters out of. You can use the following formula:

=TEXT(A1, "mmm")

This formula will extract the first three characters from the text.

3. Changing Case (Upper, Lower, Proper)

The TEXT function can also change the case of text strings. Suppose a situation when you want to make cell A1’s text value uppercase. The formula below can be used:

=TEXT(A1, "UPPER")

Similarly, you can convert text to lowercase using:

=TEXT(A1, "LOWER")

And you can capitalize the first letter of each word using:

=TEXT(A1, "PROPER")

These formulas will transform the text accordingly.

Excel TEXT Function Errors

Although Excel’s TEXT function is a strong tool for formatting data, occasionally, it may behave differently than expected. You can troubleshoot and identify remedies by being aware of the causes of its dysfunction. The following are some typical causes of the Excel TEXT function failing, along with their related fixes:

Invalid Data Type

For the TEXT function to function, a valid date, integer, or text value must be entered. The function might only function properly if the value you’re attempting to format matches the anticipated data type. Verify the formatting of the data you are using.

Solution

Verify the data type of a value before formatting it. Use Excel’s built-in functions like DATEVALUE, NUMBERVALUE, or TEXT to convert it, if necessary, to the correct data type.

Incorrect Format Code

The format code used in the TEXT function determines the desired formatting. If you enter an incorrect format code or use an unsupported code, the function may not produce the expected results.

Solution

1. Double-check the format code you’re using in the TEXT function.
2. Ensure that it follows the correct syntax and matches the formatting requirements.
3. Consult Excel’s documentation or online resources for a list of valid format codes.

Text Overflow

Unexpected outcomes may arise if the prepared content is too long to fit in the cell and spills into neighbouring cells. If the text is wider than the usual cell width in Excel, it won’t be fully visible.

Solution

To fit the prepared text, widen the columns. You can adjust the column width by hand by double-clicking the column border or by selecting “AutoFit Column Width” from the formatting menu in Excel.

Regional Settings

Excel’s TEXT function relies on your computer’s regional settings or the Excel application. If the regional settings don’t match the format code being used, it can result in errors or unexpected formatting.

Solution

Ensure that the regional settings in Excel match the format code you’re using. Go to your computer’s “Control Panel” or “Settings” and adjust the regional settings to align with the desired format.

Other Excel Text Functions

Even though Excel has several other text functions that can be used to alter further and transform text, the TEXT function is a flexible tool for formatting text values. These functions can be used with the TEXT function for more difficult formatting and data manipulation jobs. Some further Excel text function examples are provided below:

LEFT(text, num_chars)

The LEFT function takes a text string and extracts a predetermined number of characters from the left side (start). You must supply the text string and the quantity of characters you wish to remove.

Example: =LEFT(“Hello, World!”, 5) will return “Hello”.

RIGHT(text, num_chars)

The RIGHT function, like LEFT, pulls a predetermined amount of characters from a text string’s end (right side). Both the text string and the desired character count are needed.

Example: =RIGHT(“Hello, World!”, 6) will return “World!”.

MID(text, start_num, num_chars)

The MID function takes a text string and extracts a predetermined number of characters beginning at a specific place (start_num). The text string, the beginning point, and the amount of characters to extract must all be provided.

Example: =MID(“Hello, World!”, 8, 5) will return “World”.

LEN(text)

The length (number of characters) of a text string is returned by the LEN function. To determine the length of a string for further analysis or formatting needs, it is frequently employed.

Example: =LEN(“Hello, World!”) will return 13.

CONCATENATE(text1, text2, …)

The CONCATENATE function combines many text strings into a single string. Text strings separated by commas may be included in any number.

Example: =CONCATENATE(“Hello”, “, “, “World!”) will return “Hello, World!”.

UPPER(text) and LOWER(text)

The UPPER and LOWER functions switch the case of a text string from upper to lower. These strategies are useful when comparing strings without considering case sensitivity or standardising text cases.

Example: =UPPER(“Hello, World!”) will return “HELLO, WORLD!”.

PROPER(text)

The Proper function raises the initial letter of each word and lower cases the succeeding letters. This function is widely used to maintain constant text capitalization. 

Example: =PROPER(“hello, world!”) will return “Hello, World!”.

SUBSTITUTE(text, old_text, new_text)

The SUBSTITUTE function substitutes fresh text for each instance of a specified text in a string. The original text string, the desired replacement text (new_text), and the altered text (old_text) are all provided by the user.

Example: =SUBSTITUTE(“Hello, World!”, “Hello”, “Hi”) will return “Hi, World!”.

Conclusion

The TEXT formula in Excel is a versatile tool for formatting numbers, dates, and text values. With its wide range of format codes, users can customize the appearance of data according to specific requirements. The TEXT function is a valuable resource if you need to format dates and times, manipulate text strings, or apply custom number formats. By mastering the TEXT function, you can unlock the full potential of Excel’s formatting capabilities and enhance the visual representation of your data.

Learn basics of MS Excel with our free course on Microsoft Excel: Formulas & Functions.

Frequently Asked Questions