Excel TEXT Function – what is it good for?

The function you will be quick to overlook

The TEXT function in Excel on it’s own is a little dull. Incredibly useful, but a little dull.

When should you use the TEXT Function?

One thing the TEXT Function is really good for is cleaning data. And we all know that data cleanliness is… that’s right the quickest way to your data analyst’s heart. Give them clean, well formatted data, and they will be putty in your hands.

(insert fast scrolling text with voiceover protecting me from any lawsuits or complaints if the above does not happen).

If the TEXT Function is so dull why are we even discussing it?

I will refer you to my previous point. And raise you with usefulness.

The TEXT Function can not only display a number in a specific format. It can also extract information from dates to display month, year and even time down to the second.

Which is very useful when setting up daily sales reports, WSSIs. Or anything that uses a date as the identifier of something happening.

The real magic comes when combining it with other functions.

Let’s say we are cleaning our data. Inline with the high standard of Excel etiquette we are held in high regard for. And we notice that one data download keeps missing off the leading zero for our line numbers.

To fix this, we can insert a new column at the beginning of the spreadsheet, to work as our updated line number column.

For this example our line numbers are 8 digits long. So can use either of these two formulas to add on the leading zero.

=TEXT(B2,”00000000”)

=TEXT(B2,REPT(“0”,8))

Typically speaking, in Excel, if a leading zero is “knocked off”. It’s because the number is stored/formatted numerically. For the leading zero to be added, and kept, the number will need to be stored/formatted as text.

It is important to remember, the TEXT Function changes the format of a value to text

It is an easy thing to overlook. But is important if you need to continue using the changed cell as a number.

For example, if you were extracting the year from a date. And needed it to maintain a numerical format. You would need to convert that back into a value. With a formula that looks something like this:

=VALUE(TEXT([reference cell],”YYYY”))

The TEXT Function can also be used to spread #Exceljoy

Have you ever used one of those widgets which shows you the exact countdown to a specific date/event? Well you can do that in Excel too.

The formula you need is this:

=INT([end date][start date])&” days “&TEXT([end date][start date],“h”” hrs ““m”” mins “””)

And to get really fancy with it, you can add this formula into your start date:

=TEXT(TODAY(),“DD/MM/YYYY”)&” “&TEXT(NOW(),“H:MM AM/PM”)

Now every time you open the sheet, or refresh formulas, your countdown will update.

Add some formatting to jazz it up a bit and you have your own handmade countdown widget. Winning!

Like I said the TEXT Function, a little dull. But very useful.

Until next time,

Ax

3 replies on “Excel TEXT Function – what is it good for?”

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.