Excel Text Function

The TEXT Function in excel is used to convert a numeric value into a text string in a format specified by the user. The TEXT Function is largely used for presentation purposes, to make analysis or models visually appealing and easier to read. Our goal for today is to take you through the TEXT function, highlighting ways you can better format your numerical data for an aesthetically pleasing presentation in excel. Let’s take a quick look at the Syntax and argument before we delve into examples.

Syntax & Argument

The syntax of the TEXT Function is as follows:

=TEXT(value, format_text)

There are 2 arguments for the TEXT Function, and both are required:

• Value: This is the numerical value you want to convert to text
• Format_text: This represents the text format you want to apply to the resulting text string

Now let’s move on to the usage notes of the TEXT Function

Usage Notes

The below are some of the usage notes of the TEXT Function

• The first argument of the TEXT function (Value) is the Value you want to convert to a particular format whilst the second argument (Format_text) is the format code that determines the text output of the first argument.
• The TEXT Function returns a #VALUE error if the format code is not valid or ommitted.
• The maximum number of characters that can be returned by the Text Function is 255 Characters.
• The TEXT function can’t be used for calculations or deriving the final value. It’s purpose is solely to convert the input value to text, meaning that the input value has to be ready and not contingent on the TEXT Function calculating it.
• The TEXT Function is often used with other functions such as the DATE, TIME and NOW Functions for creating custom date and time formats. We would treat some examples illustrating this under our examples section.

TEXT Function Examples

Format Dates

By using the TEXT function, you can dictate how you want any date to appear in your excel spreadsheet. In our example for this section, we have two dates, one in the old fashion format of a forward slash (“/”) separating the three elements in a date (day, month and year), and the other appearing purely as a number.

For the first date, we need it to output as day, abbreviated month and the year, whilst for the second date in number format, we need that to output as abbreviated month and abbreviated year. To achieve this, we use the combination below:

Format Numbers as Currency

Next stop is formatting numbers to output a particular currency.

The first number (250) should be United States dollar symbol preceding it whilst the second number (1200) should have a Nigerian number preceding it. Let’s look at the syntax required to achieve this.

Format Decimals to Percentages

Decimals or whole numbers can be expressed as percentages with 1 being 100%. Decimals below the whole number 1 have a percentage lower than 100% whilst decimals above the whole number 1 have a percentage higher than 100%. Let’s see some examples.

To output the decimals above as percentages, we use the formula below:

Concatenating Number to Text String

Our last example is on concatenating a number to a text string in a particular format for the number. We would show two different examples and output a combination of text and number using the ampersand (&) and the CONCATENATE Function. Let’s first see our numbers below:

The first number (0.25) is a company’s Ebitda margin improvement whilst the second is today’s date, and we need to output in a specified format. Let’s see our formulas with the Ampersand for the first and CONCATENATE Function for the second.