• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel TEXT Function – handy but limited…or is it?

You are here: Home / Excel Formulas / Excel TEXT Function – handy but limited…or is it?
Excel TEXT Function – handy but limited…or is it?
January 6, 2014 by Mynda Treacy

At first glance the Excel TEXT function appears quite limited. Its purpose is to convert numbers to text in a specific/custom format.

There are two key points here:

  1. The numbers become text. This means you can’t use them in any math type of formulas.
  2. On the upside you can format the numbers anyway you want…almost!

Hold up!

Why would you want to convert a number into text when it means you then can’t use that number in any math calculations/formulas?

Wouldn’t you just format the cell with the number format you want?

The answer is ‘yes’ most of the time, but I’ll show you a clever use for it in a moment.

Excel TEXT Function

First, the syntax is:

TEXT(value, format_text)

Where the value is the number or reference to the cell containing the number you want to format, and format_text is the format you want, enclosed in double quotes.

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Let’s look at some examples.

Excel TEXT Formula Examples

Examples of Excel TEXT formulas

Example 1 – Format as a Currency.

Excel TEXT function example 1

If you don’t know the character codes to use you can refer to the Format Cells dialog box (CTRL+1 to open it) to get the number format ‘code’ (that is the $#,##0.00 part) you need:

Examples of Excel TEXT formulas

You can use any number formats for the format_text argument that you find in the Format Cells dialog box under the ‘Number’ tab.

Or you can write your own custom format.

If you're new to custom number formats check out my comprehensive guide to custom number formats.

Example 2 – Formatted as a date with hyphens instead of the Excel standard forward slash.

Excel TEXT formula example 2

Note: 41640 in cell A5 is the serial number for the date 1st Jan 2014. More on understanding dates in Excel here.

Example 3 – Display day of the week.

Excel TEXT formula example 3

Again we’ve taken a date in cell A6 and with the TEXT function we’ve only displayed the day of the week. If you wanted to display the full name of the day you would use “dddd”.

Example 4 – Add leading zeros to a number.

Excel TEXT formula example 4

This is handy for invoice numbers or other numbers/codes that require a leading zero.

Limitations of TEXT Formulas

As I mentioned at the beginning, the above examples all convert the numbers in column A to text in column B.

Examples 3 and 4 are ok formatted as text (the day of the week and leading zeros) as they are unlikely to ever be used in a math formula so having them as text won’t cause you a problem.

But it’s not ideal for examples 1 and 2 (format as currency and date), as it’s likely you’ll want to perform a calculation using those numbers at some point in your worksheet’s life.

A better option for examples 1 and 2 is to just apply the number format to the cell in column A.

Combine Text and Numbers in One Cell

I think the best use of the TEXT function is to use it to combine text and numbers in one cell.

As you can see in cell A16 below:

combine text and numbers in one cell using TEXT function

In the formula bar you can see I’ve used the ampersand symbol (&) to combine the text, “Sales up” and “Since 2010”, with the number returned by the TEXT function, which I've formatted as a percentage:

="Sales up "&TEXT(B14/B11-1,"0%")&" Since 2010"

Which results in one text string:

Sales up 95% Since 2010

You’ll notice in this example I’ve also performed a calculation for the value argument of the TEXT function, which is then formatted as a percentage:

TEXT(B14/B11-1,"0%")

Which results in:

95%

The benefit of using a formula for the value argument is that any changes in the data will automatically be reflected in my statement in cell A16.

Bonus Trick

Now you can link cell A16 to your chart title and not only will the chart update if the numbers change, but so will the title:

dynamic chart title

To link the chart title to cell A16:

  1. Left click the chart title to select it
  2. In the formula bar enter = then click on cell A16
  3. Press ENTER

Crazy TEXT Function Trick

At the very beginning I said the TEXT function converts a number to text which means you can’t perform any math calculations on the results.

However, in this video Mike Girvin, of Excel Is Fun, shows a crazy trick (at 3:30) with the TEXT function to convert a date into the right format. Even MrExcel doesn’t believe it will work:

Explanation: in the video Mike performs a calculation on the text function which in turn converts the value in the cell back to a number.

Similarly, you can also convert a cell containing a text function back to a number by multiplying the cell by 1, or add 0, or use the VALUE function, to name a few.

Note: There are some limitations to this. For example you can’t convert the text 'Wed' in example 3 above back to a number, but you can for the other examples.

Excel TEXT Function – handy but limited…or is it?

More Text Formulas Posts

Extract Text from a Cell using MID Formulas

Extract Text from a Cell using MID Formulas

Excel Test if a Range Contains Text, Numbers or is Empty

Excel Test if a Range Contains Text, Numbers or is Empty

Excel CLEAN Formula

Excel CLEAN Formula

The Excel CLEAN Function can help you to remove unwanted characters that are often imported when you copy data from web pages, but it has some limitations
Excel SUBSTITUTE Formula

Excel SUBSTITUTE Formula

The Excel SUBSTITUTE function replaces new text for old in a text string. It's an alternative to using Find and Replace by retaining the original data.
Excel Factor Entry 3 Re-format Data Using Formulas

Excel Factor Entry 3 Re-format Data Using Formulas

Use Excel's Text functions to re-format data imported from external sources
Excel SEARCH and You Will FIND

Excel SEARCH and You Will FIND

Excel SUBSTITUTE Function Trick

Excel SUBSTITUTE Function Trick

Excel TRIM Function Removes Spaces From Text

Excel TRIM Function Removes Spaces From Text

Excel TRIM function removes spaces from the start and end of text, while leaving spaces between words untouched.
Excel UPPER LOWER and PROPER Functions

Excel UPPER LOWER and PROPER Functions

Microsoft Excel’s T Function

Microsoft Excel’s T Function

The Excel T function checks whether a value is text, and returns the text if it is, or returns double quotes (empty text) if it isn’t.

More Excel Formulas Posts

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.


Category: Excel FormulasTag: text formulas
Previous Post:https://www.myonlinetraininghub.com/assign-a-macro-to-a-shape-or-use-a-shortcut-sequenceAssign a Macro to a Shape or Use a Shortcut Sequence
Next Post:Mac Excel Keyboard Shortcuts CoverMac Excel Keyboard Shortcuts Cover

Reader Interactions

Comments

  1. Roy

    August 9, 2019 at 12:19 pm

    No worries about dealing with TEXT() results being text representing numbers so long as the facts on the ground do not make the following bearable to enter. With the thought above of a column of invoice values to add up, it would work like a champ:

    {=SUM(VALUE(J1:J7))}

    43 individual things to wrap like that in disparate places in the workbook, years later, well, yeah… unbearable. I prefer formatting the cells though, as suggested since it is more directly on the problem.

    For Lee’s question, just below (I’m sure he sent it in and was answered, but someone reading here woudn’t see the answer he got), an IF() test using ISBLANK() would achieve what it seems he wants:

    =IF(ISBLANK(A1),””,TEXT(A1,”#,##0.00″))

    (just picked a format, of course). Works for true blanks (“nulls”) and sort of blanks like =”” creates.

    Reply
  2. Lee Borsos

    July 14, 2016 at 7:32 pm

    If a cell in column A is empty, can you make the formula return a blank cell for the TEXT function as I am struggling with this for some reason.

    Reply
    • Mynda Treacy

      July 14, 2016 at 10:11 pm

      Hi Lee,

      I’m not sure if the cell in column A is empty it’s the same as a blank cell. Can you please give me an example of what you mean?

      You might like to post your question on our Excel forum where you can also upload a sample workbook.

      Mynda

      Reply
  3. Allen Reidhead

    June 19, 2014 at 3:30 am

    Excellent instruction – So clear and precise.
    Now what about aligning text boxes in a chart that are setup vertically on right side of chart as column. I am in Dashboard course with Avg Cost numbers on right side of chart, and am unable to align them.

    Reply
    • Mynda Treacy

      June 19, 2014 at 9:58 am

      Thanks, Allen 🙂

      You’ll find the alignment tools for text boxes in the ‘Drawing: Format’ tab of the ribbon which is active when the text box is selected. So, select all of your text boxes that you want to align, then in the Format tab: Arrange group use the Align tools.

      To select multiple text boxes just select 1 and then hold down SHIFT to select more. You can also select 1 and then press CTRL+A to select all objects, then you can unselect the ones you don’t need by holding down SHIFT while you click on the ones you don’t want.

      You might also like this free add-in from Excel Campus that allows you to
      use arrow keys to align the chart elements

      Mynda

      Reply
  4. Jon Acampora

    January 9, 2014 at 7:26 am

    Thanks for the great article! That’s one crazy trick that Mike Girvin figured out.

    This is one format I use quite often to convert dollars to millions.

    =TEXT(A4,”$#,##0,,.0″)&”M”

    Reply
    • Mynda Treacy

      January 9, 2014 at 11:36 am

      Cheers, Jon.

      You can also add the ‘M’ inside the formula like this:

      =TEXT(A4,"$#,##0.0,,\M")

      Kind regards,

      Mynda.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.