The Function you'll be quick to overlook
The TEXT Function is unlikely to be listed as “need to know” on a job application for a Merchandiser or Ecommerce Manager, unlike the VLOOKUP. However, the usefulness of the function is understood you’ll be glad to know it.
This post will cover what the TEXT Function is and outline some examples of when it can be of use for Merchandisers and Ecommerce Managers.
What is the TEXT Function?
Why is the TEXT Function Important?
What makes the TEXT Function important, is how it can be used to standardise data formats. And therefore helped to cleansing, clean up data, so that it is easier to read and or use.
The TEXT Function can be used to create a consistent value format for reference cells, that are to be used in other formula calculations. The most common use for the TEXT Function is to format dates so that they are more readable.
When would Merchandisers use the TEXT Function?
The TEXT Function can prove useful to Merchandisers when leading zeros need to be included in SKUs or line numbers that may otherwise be removed when data is downloaded in a csv file.
When would Ecommerce Managers use the TEXT Function?
The TEXT Function can prove useful to Ecommerce Managers when date formats need to be more easily read. The TEXT Function is also useful when needing to create pre-set summaries in regular performance update reports.
Let's look at some examples of the TEXT Function in action
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.
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!
Tips and Reminders for TEXT Function
A couple of things to keep in mind when it comes to using the TEXT Function.
It is important to note that the TEXT Function converts a value into text. Meaning any other formulas using that reference cell as a value will not return the expected result.
One way around this is to combine the VALUE and TEXT Functions, that way the value will be formatted as needed and still maintain its value characteristics. =VALUE(TEXT([reference cell],”YYYY”))
Finding an exact match for your needs on the internet can, at times, feel like trying to find the missing pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help, think of me as your Excel Agony Aunt, send in your lonely heart letters and I will find your perfect formula partner.
Like this post? You should see my newsletter.
Subscribe to the Excel & ME newsletter to get updates that will empower you to inform your business decisions with data.
