How to Use the Google Sheets TEXT Function [3 Easy Examples]

Google Sheets intelligently formats data correctly when you write it into a cell. However, often times we want to write our data in a different format than the one it automatically assigns. In these cases, the Google Sheets text function can be convenient.

This article discusses the TEXT formula, the syntax, and a few examples to demonstrate the formula. Read on to master this area of Google Sheets.

What Is the TEXT Function in Google Sheets?

The TEXT function in Sheets allows you to change a number into text. It benefits people who wish to display the value in a cell in a specific format. For example, let’s say a column contains percentages. In Sheets, 30% will be stored as 0.3, which is significantly more challenging for an average user to read.

Using the TEXT function in Google Sheets, you can also force it to express numbers in another format and combine characters or text with numbers.

You can also use the Google Sheets TEXT function to display dates in a specific format. Note that you cannot use the TEXT function to extract numbers from a string.

Syntax for TEXT in Sheets

The Google Sheets text formula is simple and only requires two parameters. Both are needed for the formula to work correctly. Here is the syntax for the formula:

=TEXT(num, format)

The formula is relatively simple. Here are the parameters needed for the formula to work:

• num: This is the time, number, or data you wish to change into another format.
• format: this is the pattern you want the data in the num parameter to follow. This needs to be enclosed inside quotation marks.

The format parameter can use two types of symbols for formatting. These are:

1. 0 – this parameter forces zeros to be displayed if the number has fewer digits than the format specified.
2. # – this input will work similarly to 0, but it won’t force the function to display zeros on any side of the decimal point.

Here are a few things to know about Google Sheets text functions:

• The format parameter of this function cant have an asterisk (*) symbol.
• The function can’t support the ? pattern.
• The TEXT function doesn’t support a fractional format pattern.
• The date and time patterns and # and 0 can’t be mixed together.

The function supports these time and date patterns in Google Sheets text format:

• d – Displays the day of a month as one digit.
• dd – Displays the day of a month as two digits.
• ddd – Displays a shortened name for the day of a week.
• dddd – Displays the full name for the day of a week.
• m – For dates, this parameter will represent the month in a year in one or two digits. For time, this parameter will describe the minutes in time. The month will be represented by default unless hours and seconds are provided as part of the time in this parameter.
• mm – This works the same way as m, representing the month in a year as two digits. If used in time, then it will represent the minutes. This will default be used to represent a month unless hours or seconds are used beside it to represent time.
• mmm – This represents a short name for the month in a year.
• mmmm – This will represent the full name for a month in a year.
• mmmmm – This is used to show the first letter for a month in a year.
• yy – This will represent the year as two digits.
• yyyy – This represents the year as four digits.
• HH – This is used to represent hours in the 24-hour format.
• hh – This is used to represent hours in the 12-hour format.
• ss – This is used to represent the seconds in a time.
• ss.000 – This represents milliseconds in time.
• AM/PM – This is used to show the hours depending on the 12-hour clock representing AM or PM in the time of day.

Note that capitalization and the number of letters are crucial to getting your desired output.

Related Reading: Count Cells with Specific Text

How to Use the TEXT Function

Here are a few examples to help you understand the TEXT Google Sheets function works.

Time

This example shows the time in a “hh:mm:ss AM/PM” format. We wish to change this into a “HH:mm” format, which will convert it from a 12-hour format to a 24-hour one.

Here are the steps you need to follow to do so:

1. Click on the cell where you want to input the formula.
2. Type in the initial part of the formula, which is =TEXT(.
3. Now type in the parameter, which is the number you wish to convert to text. In this case, we are using the cell reference A2.
4. Add a comma and then write the second parameter, the format we wish to turn the text to. Using the syntax we provided above, type the parameters inside quotation marks. In this example, we write it as “HH mm”
5. Add a closing bracket to finish the formula, and then press Enter.

Date

In this example, we have the date in the “dd/mm/yyyy” format. Here, we will change this to a “dd mmmm yyyy” format.

Here are the steps you need to follow to do so:

1. Click on the cell where you want to enter the formula. Now, type in the initial part of the formula, which is =TEXT(.
2. Now type in the first parameter, which is the date. In this case, we are using the cell reference A3.
3. Add a comma and then write the second parameter, the format we wish to turn the date to. Type the parameters inside quotation marks. In this case, we write it as “dd mmmm yyyy”
4. Add a closing bracket to finish the formula, and then press Enter.

Currency

In this example, we have currency in a cell that we wish to turn into a whole number.

Here are the steps you need to follow to do so:

1. Click on the cell where you want to enter the formula and type in the initial part.
2. Now type in the first parameter, the cell containing the currency. In this case, we are using the cell range A4.
3. Add a comma and then write the second parameter, which is the format for the currency. Type the parameters inside quotation marks. In this example, we write it as “$#”
4. Add a closing bracket to finish the formula, and then press Enter.

Google Sheets Text Function Frequently Asked Questions

What Is the TEXT Function in Google Sheets?

The TEXT function in Google Sheets allows you to change a number into text. There are several types of data sets that you can change by using this formula. These include dates, time, currency, and percentages. You can also specify the format using which you want the text to be displayed.

How Do I Use the TEXT Formula in Google Sheets?

The TEXT formula is =TEXT(num, format), where num represents the number or the cell address containing the number which needs to be converted into text. The format represents the method by which you want to display the data. You can use 0 in the format parameter to display the exact values with the zeros, or you can use # to remove the zero from the left and right.

How Do I Show Text Instead of Formula in Google Sheets?

You can add an apostrophe sign before the equal sign in a formula. This will force Google to consider the entire contents of a cell as text. Rather than performing a calculation in the cell, the contents will be displayed as they are. You can also append text with the CONCAT function.

Can You Turn Text in Google Sheets?

You can use the Text rotation option in the main toolbar of Google Sheets to rotate the text according to your liking. You can either select presets or define an angle with which you want to turn the text in your spreadsheet.

Texting Away

Although the name of this function would imply it just adds text to a cell, you just need to keep in mind that it controls number formatting.

It’s quite annoying to try to remember all the format codes for the Google Sheets TEXT function, but you can always bookmark this page to check, or you can change formatting by navigating to Format > Number > Custom X instead. This can also make it easier to clear formatting.

Related Reading:

• How To Make Text Vertical In Google Sheets (Rotate Text)
• How to Insert Text Box in Google Sheets
• How to Count Cells with Specific Text in Google Sheets (Simple Formula)
• How to Change Text Case in Google Sheets (Upper, Lower, or Proper)
• How To Wrap Text in Google Sheets With 1 Simple Click