12. TEXT: Format text to be all pretty. Startup and investor excel model training

Intro to Excel model training- TEXT

This is part of a series on Excel model training focused on helping founders and investors to actually enjoy and get proficient in excel. It’s such an important skill to have, and there’s no excuse to not be able to do all the ‘advanced’ stuff, let alone the basics.

This is a pragmatic course where you will only learn the most useful things; those that I actually use regularly. We’ll go through the obvious applications, but also include some hacks I’ve developed over the years.

To get the excel example for this and all the other instalments, subscribe with the pretty box on the right and you’ll get the excel tip of the day sent directly to your inbox. Within a month and ten minutes practice, you’ll be a boss in no time.

You can join up to the training here.

What’s the point of the TEXT?

In our last class we stuck stuff together using & (ampersand). I alluded to making stuff a bit prettier by including something called TEXT in a GIF so that dates would show.

Since we only stuck stuff together, it was ugly. This class you will do the exercise again but everything is going to look awesome.

Text basically allows you to make the stuff you stick together in a text string to look as god intended.
Let’s say you join the 3 comma club and you want to show the 2 billion, or 2000 million since that sounds like more money. Wait, one of the commas was just missing. Can’t have that. See below how we show the comma in the number.
2017-07-24_14-17-28.gif
Now, let’s say you want to get more fancy… but now you’re thinking crap, do I really have to learn how to do all these nerdy format things?!
Well no, there’s a trick. Open up custom formatting and jack your systems’ swag.
Copy the format you want between the inverted commas and boom.
See here:

text.gif

You can apply this method to pretty much anything from numbers to dates. Rememeber though, your goal is to get things done. Don’t play with this stuff too much as it’s not a massive use of time. These things should net net save you time.

How do I do these things you speak of?

Well watch the GIFs above to see examples.

Simply put, you do this:

=TEXT(

Cell – what you want to format

” format code ” ) – Between the inverted commas you insert your format code.

=TEXT(Value you want to format, “Format code you want to apply”)

Nothing will work if you don’t put the format between the inverted commas. You will forget this from time to time and yes you will get pissed off 😉

Here are some examples I jacked from Microsoft.

Obviously the numbers in the ‘value you want to format’ are links to cells, to make this valuable…

Some of these examples are totally pointless (for most people) but it serves to show the extent of the possibilities.

 

Formula Description
=TEXT(1234.567,“$#,##0.00”) Currency with a thousands separator and 2 decimals, like $1,234.57. Note that Excel rounds the value to 2 decimal places.
=TEXT(TODAY(),“MM/DD/YY”) Today’s date in MM/DD/YY format, like 03/14/12
=TEXT(TODAY(),“DDDD”) Today’s day of the week, like Monday
=TEXT(NOW(),“H:MM AM/PM”) Current time, like 1:29 PM
=TEXT(0.285,“0.0%”) Percentage, like 28.5%
=TEXT(4.34 ,“# ?/?”) Fraction, like 4 1/3
=TRIM(TEXT(0.34,“# ?/?”)) Fraction, like 1/3. Note this uses the TRIM function to remove the leading space with a decimal value.
=TEXT(12200000,“0.00E+00”) Scientific notation, like 1.22E+07
=TEXT(1234567898,“[<=9999999]###-####;(###) ###-####”) Special (Phone number), like (123) 456-7898

Example

The excel example sheet will help you to learn this with an example.

 

To get the training model, subscribe to the series on the right.

Get in the game

Free tools and resources like this shipped to you as they happen.

We won't send you spam. Unsubscribe at any time. Powered by Seva