EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips Text Formula in Excel
 

Text Formula in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated August 21, 2023

Text Formula in Excel

 

 

Excel Text Formula (Table of Contents)
  • Text Formula In Excel
  • How to Use Text Formula in Excel?

Introduction to Text Formula in Excel

While working with Excel, we handle various data types like Integers, Float, Boolean, Text, etc. Out of them, Text is one of the data types which we use widely. In this, we will cover the definition of the text function and different ways to use the TEXT() function with examples.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

The function that converts the numbers or values into text format specified by the user in the format_text and returns the text is called Text() Formula.

Syntax of TEXT() Formula

Text Formula Syntax

Below is arguments of the Text Formula:

  • Value: The data in a non-text format that we want to convert into text format.
  • Format_Text: The format in which the user expects the text should be Format text which should be in double quotes.

How to Use Text Formula in Excel?

Below are the different examples of using text formulas in Excel.

You can download this Text Formula Excel Template here – Text Formula Excel Template

Example #1

Input a number in Excel and see how it looks; normally, it will be considered in a general format.

Text Formula Example 1-1

We can convert that to a number by selecting the number format from the drop-down, then it will convert to a number format with zeros after the decimal point as below.

Text Formula Example 1-2

If we want to add zeros at the beginning of the number like below.

Text Formula Example 1-3

Now the movement, we move the cursor to the next cell; Excel will automatically clear the leading zeros. Then what is the solution if we want to add the leading zeros? The answer is TEXT() Formula.

For adding leading zeros, we need to give the format code 000. The number of zeros depends on the requirement.

Observe the below screenshot.

Text Formula Example 1-4

One leading zero is added when we give three zeros for a two-digit number. When we gave four zeros, it created two leading zeros.

Example #2

The date format is not similar in all the countries; a few countries use date first, and a few countries will use month first. We may find it difficult to understand the correct date in those cases. Using the Text() formula, we can also convert the date format into text format.

Text Formula Example 2-1

Observe the above date, 2/3/2019. Here it may be March second or February third.

We can find the exact date if we apply the TEXT() formula to this.

text formula example 2-2

Observe the above screenshot. The text format is “DD-MMM-YYYY”, and the result is 03-Feb-2019. First, we assume the date is 02 – Mar -2019, but after applying the formula, we learned it is 03 – Feb – 2019.

DD represents a day with a number without leading to zero; again, this DD can be used differently.

DD – represents the date in number format; if it is a single number, it will come with a leading zero.

DDD represents the date in string formats like MON or TUE.

DDDD – represents the full form of the day, like MONDAY or FRIDAY, etc.

M – represents the month with a number without leading zero.

MM – represents the month with a leading zero.

MMM – represents the month in string format with the month’s first three letters.

MMMM – represents the full name of the month.

Similarly, for a year also.

We can set the format for a time and also with the below format text codes.

H – represents hours without leading zero.

HH – represents hours with leading zero and, similarly, for minutes and seconds. Below is the example screenshot.

Text Formula Example 2-3

Example #3

When we want to display the percentage or currency or numbers after decimal points, the TEXT () Formula can be achieved.

We will see a few examples of that text format codes.

Text Formula Example- 3

When we want two zeros after a decimal point, use “0.00” in the text format.

If we want the number to represent in dollars, give “$###” in the text format.

If we wish to change the percentage, give “#%” in text format.

If we want the number separated by commas, then give “###,###” in text format.

Below are the Different Text Format Codes

0 – Represents the insignificant zeros. In the above example, if the number is 500.5, it will give the result as 500.50 in the first line formula.

# – Represents to display numbers. Giving “#.##” for 500.5 will result in 500.5 as it does not display insignificant zeros.

– Represents for separation of the numbers. If we want to separate the numbers by comma, we can use the combination of #.

. – Represents a decimal point.

Special Characters

If we want to add any special characters, we can add them in between the double quotes; it will display in the same way how we insert them. Find the below screenshot for reference.

Special characters-1

Concatenation of Texts

We can concatenate texts with numbers or perform some calculations, and the results can be displayed in the required format.

Consider a small calculation like in a class, 50 students are available, and there are 4 classes, and the fee for each student is 1000 rupees.

Concatenation of texts formula

We can now calculate in the text function and display the results in the desired format.

If we want the result in Total cell as “Total fee for all children is ###,###”, we can achieve this using a combination of text and concatenation function.

Here “Total fee for all children is “a string and the number results from the calculation.

Concatenation of text -1

So the result will be as given below.

Concatenation of texts -2

There is a function to determine whether the data in a cell is text. So we can check whether we converted to text or not.

Observe the above screenshot; we checked the data we converted using the text formula completely in text format. The result from the formula is True, which means the data in cell I42 is in text format.

Concatenation of texts -3

Things to Remember About Text Formula in Excel

  • TEXT() is a very simple and understandable function to use.
  • Do not forget to keep the text format in double quotes. If we missed keeping in double quotes, the formula would give the error result as a #value.
  • Before converting to text using the Text() Formula, we can check whether the data is in text format using IS TEXT() Formula.
  • Use & symbol between string and text Formula to concatenate string and number operations.

Recommended Articles

This has been a guide to Text Formulas in Excel. Here we discussed How to use Text Formula in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

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

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download Text Formula Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Text Formula Excel Template

EDUCBA

डाउनलोड Text Formula Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW