Function TEXT

Let`s now check 2 practical examples on how to work with the Excel function TEXT.

Example 1 – Standard Format Codes for the Function TEXT

The function TEXT lets you change the way a number appears by applying formatting to it with “Format Codes”. Let’s work out this first example which is related to dates.

In column B of the table above we have the date of 31^st of March 2023 displayed 8 times and our task here is to extract different pieces of information from that same date by using the function TEXT. We can start by inserting the function TEXT in cell D4. This function requires 2 arguments:

1. “Value”: This is basically the reference date of 31^st March 2023 available in cell B4.

2. “Format Text”: This is the format code available in cell C4. This argument is normally used within quotation marks (“ ”) but in the case of dates, we have standardized format codes for them. The code “yy” for instance basically means you will be extracting the last 2 digits of the year of the reference date. So, in this case simply link this argument to the cell C4. If you close brackets and press ENTER, the result 23 is returned as expected.

Other standard Format Codes for dates

Before, we complete this example, let`s check the definition of each of the remaining “Format Codes”:

1. “YYYY”: It extracts the 4 digits of the variable “year” of your reference date (2023 in this case).

2. “MMM”: It extracts the first 3 letters of the variable “month” of your reference date (“Mar” in this case) through your function TEXT.

3. “MMMM”: It extracts all letters of the variable “month” of your reference date (“March” in this case).

4. “D”: It extracts the specific digit related to the “day” of the month of your reference date (31 in this case).

5. “DDD”: It extracts the first 3 letters of the “day” of the week of your reference date (“Fri” in this case) through your function TEXT.

6. “DDDD”: It extracts all the letters of the variable “day” of the week of your reference date (“Friday” in this case).

7. “MM/DD/YYYY”: This is an interesting one. Most of the countries use the date format convention of DAY > MONTH > YEAR. In US it`s the other way around (MONTH > DAY > YEAR). So, this specific code basically swaps the position between the variables “day” and “month”. (03/31/2023 in this case).

If you expand the function from cell D4 to the entire range D4:D11, your results will be displayed like in the image below:

Example 2 – Customized Format Codes for the Function TEXT

In this example, we need to display the same value 300,500 in 6 different formats. Follow the steps below for the first value of cell F4:

1. So, insert the function TEXT in cell H4.

2. Then select cell F4 as the argument “value” in your function TEXT.

3. After that, the argument “format codes” is the one defined in cell G4. As this is not a standardized code (like in “dates”), here you need to write your code using quotation marks to get the format you need. So, in this case insert quotation marks, then insert the dollar sign. After that,  leave a space, and then write the generic number 300,500 with “zeros”. At last, insert a comma for the thousand separator and close quotation marks (i.e. “$ 000,000”).

4. Finally if you close brackets and press ENTER, the correct value and format will be displayed on the screen.

Other customized Format Codes for dates

You can then copy the function TEXT from cell H4 and paste it to the entire range H4:H9 and we can adapt the arguments of those functions to get the results we need:

1. “$ k”: Here we need to display the same number in thousands of dollars. So, press F2 in cell H5 to edit this function. In the argument “Value”, let`s divide cell F5 by 1,000 and the “format text” should now read as “$ 000 k”. If you press ENTER, your result will be $ 301 k.

2. “$ k (1 Decimal)”: Here we need the same number above but with 1 decimal at the end. So, again divide the argument value by 1,000 while the argument “format text” needs to read as “$ 000.0 k”. The end result should be $ 300.5 k.

3. “$ k (2 Decimals)”: Same adjustments as the cell above but with one more digit for the decimals (i.e. “$ 000.00 k”). Your final result should be $ 300.50 k.

4. “£”: Here we basically need to change the currency format from dollar ($) to British Pound (£). So, in this case there`s no need to divide the original number by 1,000. Simply use the code “£ 000,000” and your function TEXT will return you the value £ 300,500.

5. “£ (2 Decimals)”: Same number as above but the code this time needs to have 2 decimals (i.e. “£ 000,000.00”).

After these adjustments your example 2 should look like the image below:

Bonus Tip: Alternative method to customize your format codes

Let’s now check how to find more codes and how to create your own format code. First, copy cell F9 and paste it in cell H9. So, now there`s no format applied to this cell H9 anymore. With that cell selected, follow the steps below:

1. Press CTRL + 1 to open the “Format Cells” menu.

2. In the field “Category” choose the option “Custom”. There you will have access to several different codes which you can simply copy and paste as the argument “Format Text” of your Excel function TEXT.

3. Now, if you want to customize your own format code, you need to do it in the field “Type” of the same menu.

4. Here we need to type the generic code we want which in this case must be “£ 000,000.00”. As you are typing the code directly in this menu, there`s no need to use quotation marks this time (unlike required in the function TEXT). Another thing to note is that while you type your code the field “sample” will already show you how your format will look like.

5. As this is exactly what you need, simply click on OK and the format of cell H9 will be automatically adapted for you.

This might also be a good alternative or complement to the function TEXT. So, keep this tip in mind.