The one and only purpose of using the function T in Google Sheets is to test value in a cell and return if it’s text.
When you go through Google Sheets as well as Excel documentation regarding this function (yes, it’s available in both the applications) you can see one thing.
Both documentation undermines (I think so) the use of this function saying it’s rarely required.
But for me, the function T is useful at some points. I am going to share you where I am using the function T in Google Sheets in my formulas. You may find that info useful.
Syntax:
T(value)Normal Use of the Function T in Google Sheets
With the help of the function ISTEXT, you can test a cell and return TRUE if the value in that cell is a text. For example, in cell B1 I have the below formula.
=ISTEXT(A1)In cell A1, I have put the string “Info Inspired”. So the formula would return TRUE as the result.
You can use the function IF together with ISTEXT to return the value of A1 if it’s text, else blank.
=if(istext(A1)=TRUE,A1,)You can simplify this formula with the T function in Google Sheets.
Just use the function T as below.
=T(A1)But the function T is not the exact match to the above IF + ISTEXT combination. At one point it may return a different result, i.e., if the value is an error, T returns the error. The combo returns blank.
Here are some example T formulas.
If the cells contain a text string, special character or error as the value, the T function in Google Sheets will return that value, else blank.
How to Use the T Function in Array in Google Sheets
Google Sheets T function can be used in an array. It will help you to test multiple values at a time.
In the just above example, I have multiple formulas in the range C2:C9. The below Array Formula in cell C2 can replace all of them.
=ArrayFormula(T(A2:A9))In an infinite range, the use of the function T will be as below.
=ArrayFormula(if(len(A2:A),T(A2:A),))Test it in a Google Sheets file by yourself. So that you can understand it even better.
Before concluding let me share you a few real-life examples to the use of the function T in Google Sheets.
Real-life Use of the Function T in Google Docs Sheets
Function T in Query
The Query is prone to return invalid output in a range of data that contain mixed types of values in any single column.
So the functions like N and T will be useful to return only number or text from the column and use it in Query without any issue.
Must Read: How to Solve the Mixed Data Type Issue in Query in Google Sheets.
That’s the use of the said functions in Query. Now here I am going to show you how to extract only text string using the T function and filter out the blanks.
Function T in Filter
Column A contains text string and numbers. As you have learned, with the help of the function T, we can extract the text strings.
=ArrayFormula(t(A1:A))Of course, there will be blank rows in between the extracted string. To remove that you can use the Filter together with the Function T in Google Sheets as below.
Do remember! With the function Filter, no need to use the ArrayFormula function.
=Filter(t(A1:A),t(A1:A)<>"")Here is one bonus tip. How to return the numbers in the above example?
Just replace the function T with N and change <>""to >0.
=Filter(N(A1:A),N(A1:A)>0)Function T in Concatenation
If your data entry operator is not a professional, you may have to find ways to correct your data often. Without formatting your data, you will find it very difficult to use it on many occasions.
Here is one example. I have a dataset and in that, I am unsure about the values in column A, B, and C due to the poor data entry. See this.
Actually, I want the first name in column A, last name in column B and DOB in column C. But see how the data is formatted. It’s really in a mess!
Here you can use the T function to extract the first and last name properly.
I am using the function T in Google Sheets with the Concatenate alternative which is the Ampersand sign.
=ArrayFormula(t(A2:A11)&" "&t(B2:B11)&" "&t(C2:C11))Similarly, can you help me to extract the dates only from the column?
Yes! Here you go.
=filter(datevalue({A2:A;B2:B;C2:C}),datevalue({A2:A;B2:B;C2:C})>0)You may need to format this output back to date from Format > Number > Date.
=ArrayFormula(to_date(filter(datevalue({A2:A;B2:B;C2:C}),datevalue({A2:A;B2:B;C2:C})>0)))That’s all. Enjoy!
