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.
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!
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]))
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%”)
Using the TEXT Function changes the format of the value from numerical to text. So if you need to maintain the numerical value, make sure to use the VALUE Function as we discuss here.
And use the formatting menu to select the format you want to display.
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
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.
I can help bring your ideas to life. Let’s talk about what we can build together.
How to use array constraints with the COUNTIFS Function Formula...
Read MoreIFERROR vs ISERROR Formula Friday #20 Formula Fridays are short...
Read More