This is how to format data Formula-style!
In our 1st Formula Friday I showed you how to extract the month and year from a date using the TEXT Function. We are going to continue exploring the TEXT function with 3 more formulas you can start using right now.
How to extract week day from date
It only makes sense to kick things off with the one related option I did not share in the previous post. And that is how to extract the weekday of a date.
=TEXT([reference cell],”dddd”)
=TEXT([reference cell],”ddd”)
Now you have the power to unlock the exact day people were born!
How to pad a number with zeros
This is a really good one for those missing leading zeros.
=TEXT([reference cell],”000000”)
You can take this to the next level by using this formula. And specify how many characters should be in the text, without typing all the zeros individually.
=TEXT([reference cell],REPT(“0”,[number of characters]))
How to display a number with a custom format
There are so many custom formats you can apply to display the data. Mircosoft have a fact sheet which goes into more detail on each type of format you can use.
And I have listed few examples below:
=TEXT([reference cell],”00-0000-00”)
=TEXT([reference cell],“£#,###.00”)
=TEXT([reference cell],“#.00%”)
One thing to keep in mind
The TEXT Function is a really great way to tidy up data.
CSV files and excel downloads can be pulled from so many sources. The likelihood of them all having the exact same format is slim. But with a little sprinkling of Excel magic anything is possible.
I hope you have found a formula you can start using in your day to day. Even if it’s just to make everyone think you know the weekday all your fave celebs were born on. Any joy that can be spread with Excel is a good thing in my book.
If you have any other burning questions, drop me a line.
Until next time,
Ax
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.
