In a previous post, we looked at the ideal Excel number formats for accounting and finance. In each example, we formatted the cell values. However, there is another way to use these number formats; we can use them inside the TEXT function to generate custom headings and dynamic text. That’s what we are looking at in this post.
Download the example file: Click the link below to download the example file used for this post:
Watch the Video
Understanding the TEXT function
The purpose of the TEXT function is to convert a number into text using a specific number format.
Syntax and arguments
- value – the number to convert to text
- format_text – a text string defining the number formatting to apply
The following is a simple example to illustrate the TEXT function.
The formula in cell D4 is:
="Training costs for September were " &C4& "."
The number 5000 in cell C4 is included in the sentence, but there is no formatting.
We can do better than that. The formula in cell D5 includes the TEXT function:
="Training costs for September were " &TEXT(C5,"$#,##0") & "."
The $5,000 in C5 is still the same number but includes a currency symbol and a thousand separator. This is much easier to read.
To understand these number format codes, check out this post: Excel number formats for accounting & finance you NEED to know
To understand more about the text function: https://exceljet.net/functions/text-function
Different context: cells vs TEXT function
When applying number formats in the TEXT function, the display context differs from applying number formats to cells. Therefore we can simplify the basic number format.
Let’s start with a common cell number format:
- Thousand separators
- Negative numbers in red with brackets
- Zero is a dash
- Correct number alignment (whitespace to the right)
When applied to cells, it displays positive, negative, zero, and text values as follows:
Ignore the text format
The 4th section of a number format code defines how text should display. However, the TEXT function already displays text. Therefore, the 4th section serves little purpose in the TEXT function. So, let’s ignore it.
Therefore, our example format changes from this:
The 3rd section of a number format code defines how zero values display. When used in a column of numbers, we may want to use a dash to represent a zero. But if we are concatenating with other text, the dash probably won’t look right. We are more likely to display zero as a number which is the default number format. Therefore, the zero format from the third position can also be ignored.
Our format changes again. From this:
When using number formats within a cell, we want negative numbers displayed in brackets/parentheses. To get the numbers to align correctly, we force whitespace to the right of positive numbers.
However, when using the TEXT function alignment of numbers is mostly irrelevant.
Our example format is simplified once again. From this:
Finally, while colors work when formatting cells, they do not work within the TEXT function.
So, our format reduces one more time. From this:
Positive and zero values will display with at least one digit and a thousand separator. Negative numbers will display in brackets with at least one digit.
What started as the following, for a cell number format
Has been reduced to the following for a TEXT function number format:
Applying format_text as cell value vs hardcoded
Within the TEXT function, the format_text argument is a string. This can be hardcoded directly into the function, or it can be a cell-linked value.
Hardcoded into function
The screenshot above shows the format_text applied as a hardcoded value.
The screenshot above shows the format_text applied as a cell-linked value. The number format is included in cell C2.
This option creates more flexibility. However, we rarely, if ever, change these formats; therefore, I believe it is one scenario where it is acceptable to include hardcoded number formats in these functions.
Within the number format, we may wish to display units to provide more context to the numbers.
We can include currency symbols by including them within the format.
We can also include \k, \m, or \b to display numbers as thousands, millions, or billions.
When formatting numbers in this way, it is common to include commas and decimal places to display the required granularity. This prevents the need to divide a number to achieve the correct display format.
This number format also works with a hardcoded value in a TEXT function.
An alternative method uses double quotes around the letters. For the cell-linked method, this is no problem. The cell value would be:
But for the hardcoded version we need to include additional double quotes to act as escape characters.
We can also use CHAR(34) to represent a double quote character. Check out this post for more information about using double-quote characters in text strings: How to add double quotes in Excel formula.
Symbols and decimal places
Symbols and decimal places are the same for both methods. So we don’t need to apply any special treatment for those.
Using number formats instead of the IF function
A typical finance scenario may be to state if a movement is favorable or adverse to a prior period or budget.
We could generate the words favorable or adverse using an IF function.
The formula in cell D3 is:
In the screenshot above, C3 is positive, showing the word favorable.
However, we can achieve this directly with TEXT without an IF function.
The screenshot above shows the word favorable in cell E6 because the value in cell C6 is positive or zero. This value changes to adverse in cell E7 because cell C7 is negative.
The formula in cell F6 shows how we can use these TEXT functions to create sentences.
="There is a " & E6 & " variance of "& D6 & " to last month"
We can achieve the same favorable or adverse value from hardcoded formats.
The formula in cell E4 is:
With this method, the escape characters make the formula much harder to understand.
The custom number formats we use for cells can also be applied to the TEXT function. This provides a great way of adding dynamic commentary/insight into reports.
As the context differs from cell formatting, we may need to think differently about how we apply the number formats. We cannot use colors, but the other functionality is still available.
We’ve seen two ways to apply this: cell-linked or hard-coded values. Depending on the method we choose, we could encounter a few syntax issues. But we’ve covered these in this post, so hopefully won’t catch you out.
- Excel number formats for accounting & finance you NEED to know
- Change number format based on a cells value
- How to add double quotes in Excel formula
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: