T Function

What is the T Function?

The T function in Excel returns the text string for the referenced values, such as text strings, special characters, boolean values, etc.

The function's name represents the abbreviation for 'text' and will return the corresponding text component when a cell value is supplied.

When you work in Excel, you encounter dates and times, numbers, boolean values, memorable characters, and text strings.

If you have thousands of rows of data and need to ensure whether all the cells have text strings, then the function can be used, which will return the empty cell for all values but the text strings and special characters.

The function also provides compatibility with other spreadsheet software apart from Excel.

It sounds like quite an unusual function.

We will see the T function, how to use the function, and a couple of examples in the subsequent sections.

Understanding T Function

The T is categorized as a text function that returns the text strings for given values.

The different types of values you would naturally input in the spreadsheet are numbers, text strings, date and time, etc.

1. Text Strings

First and foremost, since it is a text function, it is highly compatible with text strings. The objective of the position is to return a text string for any given value. What would happen if you reference a cell containing a text string?

A text string would ultimately return the exact text string. So, for example, if the value in the cell is 'Nvidia Inc,' then the result would be 'Nvidia Inc.'

If you have special characters such as @, #, $, %, &, etc., the function returns the same characters as a result.

2. Numbers

This is where the function starts exhibiting unusual behavior, which is wholly justified. Since numbers are 'non-textual' characters, the function does not recognize them and returns an empty cell.

For example, -28 and 32 both will return an empty cell as a result. It doesn't matter whether the input is a positive or a negative number; the result would always be the same - an open compartment.

3. Date and Time

In Excel, the date is stored as a serial number, whereas time is stored as a decimal number. Although we can type in literally any date, the official series begins on 1st January 1900, which is assigned the serial number 1.

Similarly, a 24-hour clock time corresponds to the decimal number falling between 0 and 1. So, for example, if the time is 12:00 PM, the decimal value equals 0.5.

The syntax for the function is:

=T(value)

where

• value - (required) the value whose text counterpart is to be returned

Example of the T Function

Even though the function returns apparent results, it can be quite a handy tool for identifying text strings in a dataset.

Suppose you have the data as illustrated below:

We will use the formula

=T(B3) 

in cell C3 and drag it down to cell C14, which gives the result:

Interpretation:

• We see some interesting results using the T function for the dataset.
• The numbers 21 and -18 empty return cells as a result. As previously stated, negative and positive numbers yield the same result.
• When you reference a cell containing a text string, the result equals the referenced cell value. For example, the text string 'Stocks' is returned as a result in cell C5.
• Even though we have special characters concatenated with the text string, it is still identified as a text value along with the special characters.
• Since date and time values are stored as numbers in Excel, we get empty cells.
• An empty cell remains unaffected by the function's capabilities and remains open.
• The boolean values TRUE and FALSE are stored in Excel as numbers 1 and 0, respectively. This is why we still get the result as empty cells even though they are text strings.
• Finally, the error values remain unaffected by the function and return the same value.

N vs. T function

If the T function returns the text strings, the N function returns the numbers for the given values.

The N is categorized as an Information function that returns the number for text strings, boolean values, date & time, etc.

The N stands for 'number,' which means that all the results are expected only to be numerical values.

1. Numbers

All the positive and negative numbers are compatible with the function and return as numbers when you use the N function.

For example, the numbers 11 and -13 will give results of 11 and -13, respectively.

2. Date and Time

The date and time, stored as serial numbers and decimal numbers in Excel, are compatible with the function.

Therefore, when you reference a cell containing date and time values, the function identifies them and returns their corresponding serial number or decimal numbers.

For example, the date 18th November 2022 will return the serial number as 44883, while the time 11:15 PM will return the decimal number as 0.96875.

3. Text Strings and Boolean values

The text strings and special characters are incompatible values that return the result as 0 when you use the N function.

For example, the text strings 'Nvidia Inc' and '&!#$' will return the result as 0 in the spreadsheet.

Finally, in Excel, we have the boolean values TRUE and FALSE stored as 1 and 0, respectively.

The syntax for the function is:

=N(value)

where

• value - (required) the value whose numerical counterpart is to be returned.

Suppose you have the data, as illustrated below:

We will use the formula 

=N(B3) 

in cell C3 and drag it down to cell C14, which gives the result:

Interpretation:

• The positive and negative numbers 21 and -18, respectively, provide the same result.
• When a cell containing text strings or special characters is referenced, we get 0.
• The date and time values return the corresponding serial numbers and decimal numbers.
• If you reference an empty cell, you will get 0.
• The boolean values TRUE and FALSE return the numbers 1 and 0, respectively.
• The error values remain unaffected by the abilities of the function.

CONCATENATE vs. T Function

Although this is not a direct comparison between both functions, we will see a use-case scenario for combining both parts to yield better results in Excel.

For example, suppose you have text strings in Excel as below:

Sometimes, when you download data from third-party websites, there is a high probability that your data might have unwanted numerical values. We can use the T function to return those numbers as empty strings in such a case.

The formula will be 

=T(B3)&" "&T(C3)&" "&T(D3)

in cell E3, which after dragging down till cell E7, gives the result:

As you can see, whenever there is a numerical character or a zero in the dataset, the value is replaced by a space character giving us a more structured result.

On the other hand, if we had just directly concatenated the cells using the formula 

=B3&" "&C3&" "&D3 

in cell F3 and dragged it down till cell F7, we would get:

In this case, even the numbers are concatenated in our results. Thus, you can use the T function to make your text strings more indistinguishable.

Researched and authored by Akash Bagul | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• TRUNC Function
• UPPER Function
• VAR.P Function
• WORKDAY Function
• XIRR Function