EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources TEXT and String Functions in Excel TEXT Function in Excel
Secondary Sidebar
Excel Functions
  • TEXT and String Functions in Excel
    • Excel CODE Function
    • Excel Find
    • TRIM Formula in Excel
    • Excel CHAR Function
    • LEFT Formula in Excel
    • Opposite of Concatenate in Excel
    • Concatenate Strings in Excel
    • Extract Numbers From String
    • VALUE Formula in Excel
    • Left TRIM in Excel
    • Substring Excel Function
    • SUBSTITUTE Function in Excel
    • Excel VALUE Function
    • Excel REPT Function
    • Excel TRIM Function
    • Excel CLEAN Function
    • Excel EXACT Function
    • PROPER Function in Excel
    • Excel LEFT Function
    • MID Function in Excel
    • LEN Function in Excel
    • CONCATENATE Function in Excel
    • SEARCH Function in Excel
    • RIGHT Function in Excel
    • FIND Function in Excel
    • TEXT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Online COURSE
  • Online Excel Data Analysis Training

TEXT Function in Excel

By Madhuri ThakurMadhuri Thakur

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

Text Function in Excel

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

Text Function in Excel syntax

  • 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:

You can download this TEXT Function Excel Template here – TEXT Function Excel Template

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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Text Function example 1

Solution:

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

=TEXT(B6,”mmmm d,yyyy”)
example 1 sol step 1

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)

example 1 explanation

  • 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 1 sol step 2

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.

Text Function in Excel Example 2

Solution:

Step 1: Place the cursor in cell D6

Note: If we use the CONCATENATE function to combine the values of columns B and C, it gives time in an incorrect format, as seen in the image below. Therefore, we need to use the TEXT function to provide the values in a proper form.

Eg step 1

Step 2: Enter the TEXT formula,

=TEXT(B6,”h:mm:ss AM/PM”)& ” “&TEXT(C6,”m/d/yyyy”)
eg 2 step 2

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

Eg 2 step 3

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

eg 2 step 4

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.

Text Function in Excel example 3

Important: In a Microsoft Excel worksheet, when we enter a value with the number of digits greater than 11, Excel converts that value into a scientific notation in a non-readable format. We can use the TEXT function to convert it into a readable format.

Follow these steps to understand this-

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
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,”############”)

eg 3 step 1

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

Eg 3 step 2

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

eg 3 step 3

Tip: We can differentiate the country code from the mobile number by inserting a hyphen (-) at the desired location in the Text Formula

So,

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

Eg 3 step 4

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

Eg 3 step 5

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,

=CONCATENATE(“Today is”,” “, TEXT(TODAY(),”dddd d mmm, yyy”))
Using Concatenate and Text Function in Excel

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?

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.

Format Codes for Text Function in Excel 2

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.

Format Codes for Text Function in Excel 3

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.

Format Codes for TEXT Function in Excel 1

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

Format Codes for TEXT Function

Format Codes used in Excel while dealing with Dates

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

Text excel function

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)

Q2

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%.

Q4

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
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
4 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download TEXT Function Excel Template

EDUCBA

Download TEXT Function Excel Template

EDUCBA

डाउनलोड TEXT Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more