Okay - quickly - one of the best bang-for-your-buck Excel (or Google Sheets) formulas, as in short-to-learn, quick-to-implement, saved-me-roughly-a-zillion-hours-over-the-years, is the TEXT() formula.
You can think of TEXT() like the formula version of re-formatting your data. I’ll get to why you’d want to do that with a formula in a sec, but below are some examples:
It’s a two-parameter formula:
=TEXT(value, format_text)
where “value” = the target cell (A1, $B3, $C$4, whatever)
and “format_text” is where you put the formatting syntax in quotations.
Formatting syntax (the “format_text” piece)
A lot of people avoid TEXT - and custom formatting in general - because at first glance, the formatting syntax looks like absolute gibberish. I promise, though, that if you take 15 minutes to test out some of the above, you’ll start to get the handle of it.
Mess around with it: Type the formula =NOW() in cell A1, type the formula =TEXT(A1,”m/y”) in cell B1, and then start messing around with everything inside the “m/y” quotations. Add m’s, h’s, d’s, slashes, semicolons, commas, dots. Try “mmm” then “mm yy” then “d” “dd” “ddd” “dddd”. See what results.
Fun fact (sort of): the formatting syntax is the same syntax that Excel uses for custom cell formatting. Select any cell, hit Cmd/Ctrl+1, and go to Custom. The syntax you see in that bottom right hand white box is the exact same syntax you can use in the TEXT() formula.
Okay but why
I’m almost always using TEXT to make a set of data more readable.
converting raw dates or timestamps into specific date/time formats
for instance, if I need a bunch of dates that look like “March 17, 2023” to look like “Mar 23” or “March” or “2023” or "3/17", so I can sum or pivot-table by month or year)
converting long-decimal numbers into more readable formats.
adding text into formatting, eg converting 8533 to "8.5k" (using the &)
there’s a lot of other uses but the above cover 95% of it. Also I guess 2 and 3 are kind of the same.
Okay but again why. why don’t you just directly change the data format?
Good question. If you have a single column of data and you won’t need to update the dataset, it’s usually faster and simpler just to change the format directly (i.e. select the column and choose a format from the ribbon). BUT TEXT() is useful if:
you need the data to be readily available in multiple formats
e.g. you need to do one table that summarizes it by month and another table that summarizes it by year
if the data’s in column A, column B can be TEXT(A1,"mmm") and C can be TEXT(A1,"yyyy")
note: there is a formulaic way to do this without additional columns but it is a pain.
you want to reduce large numbers to a more readable format without losing specificity
for instance: you want the number 821305.23 to just say "821k" but later on, you’ll still want to know that the underlying number was 821,305
having a separate column that is =TEXT(A1,"#")&”k” preserves the initial data set
the data is dynamic and always comes in the wrong format
e.g. every day you’re downloading a new set of Stripe transactions, you need each one tagged by month, but the CSV you get always formats the date as "07/01/2020 03:05:11" .
you can set up a single spreadsheet where you paste the data in starting at column C and there’s a column to the left that just says =TEXT(C3,"mmm")
In all of those? Use our friend TEXT().