TEXT Function in Excel

What is the Text Function in Excel?

The TEXT function in Excel converts numbers from the numerical format to text format by using formatting codes. We can use it to display numbers as words, including symbols, while maintaining the numeric value. The TEXT function also helps concatenate numbers to formatted text strings or symbols.
Example, =TEXT(A6,”dd mmmm, yyyy”) converts the numerical Date 27-01-2023 into the text format 27 January, 2023

Key Highlights

• We use the TEXT function in Excel to convert Dates into a specific format.
• It does not support a fractional format.
• We use the TEXT function to combine numbers with characters or text.
• We cannot combine the time, Date, #, and 0 in the TEXT function formula.
• Excel gives an error #Name if there are no quotation marks around the argument ‘format_text’.

The Syntax for TEXT Function in Excel

The syntax for the TEXT function is

• Value (required): It indicates the numeric value we want to convert to text. The value can be a number, Date, reference to a cell containing a numeric value, or any other function that returns a number or Date.
• format_text (required): It is a format you want to apply. We must provide the argument as format code enclosed in quotation marks, g., “mm/dd/yy”.

How to Use the TEXT Function in Excel?

Consider the following examples to understand the use of the TEXT function:

Example #1

Consider the below table, which shows a Date in “dd-mm-yyyy” format. We want to convert it into – “mmmm d, yyyy” using the TEXT function.

Solution:

Step 1: Place the cursor in cell C6 and enter the formula,

Explanation:

• B6: It is the cell containing an unformatted parameter (Date)
• “mmmm d,yyyy”: The open and closed double quotes indicate the output in text format.
• mmmm: Four times m indicates the month in complete spelling, i.e., January; To write month as only Jan, we need to put it three times, i.e., mmm (refer to the below image)

• d: A single d indicates Dates in single digits, i.e., 2
• yyyy: Four times y indicate the year in four digits, i.e., 2019

Step 2: Press Enter key to get the below result

Example #2

The table below shows the time and Dates in columns B and C, respectively. We want the values of the time and Date columns to appear together in column C.

Solution:

Step 1: Place the cursor in cell D6

Step 2: Enter the TEXT formula,

Explanation:

• h:mm:ss AM/PM: It indicates the desired time format
• &” “&: It acts as a separator between the two text
• m/d/yyyy: It indicates the desired Date format

Step 3: Press Enter key to get the below result

The result is combined time and Date values in single cell D6.

Step 4: Drag the formula in other cells to get the below result

Example #3

The below table shows the scientific notation of a list of mobile numbers with their country codes. We want to use the TEXT function to convert the mobile numbers into the correct and readable format.

Follow these steps to understand this-

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)

Price
View Courses

120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,547 ratings)

Step 1: Place the cursor in cell D6 and enter the formula,

=TEXT(C6,”############”)

Explanation:

• C6: It is the cell containing the mobile number
• ############: The 12-digit # codes convert numbers in scientific notation into readable format.

Step 2:  Press Enter key to get the below result

Step 3: Drag the formula in other cells to get the below result

So,

Step 4: Modify the formula by inserting a hyphen (-) after two digits in the formula, as shown below,

Step 5: Drag the formula in other cells to get the below result

Using Concatenate and Text Function in Excel Together

We want to display the present day and Date using the TEXT function.

Solution:

Place the cursor in cell B5 and enter the formula,

Explanation:

• TODAY(): Indicates the present day of the week
• dddd d mmm, yyy: Indicates the current Date with the day.

The formula combines (concatenates) the text Today is with the present day to give the result- Today is Wednesday 25 Jan, 2023.

How to Get the Format Codes for Text Function in Excel?

To locate the format codes for time:

Right-click the desired cell > Go To Format Cells > Number > Custom > Select the desired time Type from list > OK.

To locate the format codes for the Date:

Right-click the desired cell > Go To Format Cells > Number > Custom > Select the desired Date Type from the list > OK.

Shortcut: To open Format Cells,

1. Press CTRL+1 in windows, and
2. +1 in Mac

List of Format Codes for TEXT Function in Excel

The Text function accepts most format codes used in Excel number formats. Below is a table with the most common and frequently used format codes.

*In addition, we can include any of the below characters in the format code, and they will be displayed as entered.

Format Codes used in Excel while dealing with Dates

List of Built-in Excel Text Functions

Below is a list of built-in Text functions in Excel with their uses

Things to Remember

• The Text function in Excel cannot convert numbers from 123 formats to One Two Three. To do so, we have to use Visual Basics (VBA).
• The Text function converts a numeric value to a formatted text; therefore, we cannot use the result for calculation purposes.
• The ‘format_text’ argument in the text function formula cannot contain an asterisk character (*).
• TEXT function is available in the following versions of Microsoft Excel- Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, and Excel 2000.

Frequently Asked Questions (FAQs)

Q1) What are the arguments of text function?

Answer: The TEXT function takes two arguments- value and format_text.

Value: It is the number we need to format as text. The function will not apply a format code if the argument is already text.

format_text: It is a text string (format) that we want to apply to the value. We must provide the argument in quotation marks to give the correct result.

Q2) How do you use the text function in sheets?

 Answer: To use the TEXT function in Google sheets, consider the below example

1. Place the cursor in the cell where you want the result
2. Type the formula,

=TEXT(A4,”HH mm”)

The formula will convert the time format from 12 Hrs format (4:15:00 PM) to 24 Hrs Format (16 15)

Q3) What is the use of text functions in Excel?

Answer: The TEXT function lets you convert numbers into a more readable format. We can change the appearance of numbers to the desired forms by applying the format codes in Excel.

Q4) How to use text function in excel?

Answer: To understand using the TEXT function, consider the below example,

1. Place the cursor in cell B6
2. Type the formula,

=TEXT(A6,”0%”)

The formula will convert the decimal 0.565 to the percentage format as 57%.

Recommended Articles

The above article is a guide to using Text Function in Excel. To learn about more Excel-related concepts, EDUCBA recommends the below-given articles.

1. Excel Text with Formula
2. Formatting Text in Excel
3. Search For Text in Excel
4. VBA Text