In this tutorial, you will learn how to use the TEXT function in Google Sheets.
How to Use the TEXT Function in Google Sheets
Quick Navigation
The TEXT function in Google Sheets is a powerful and flexible function that allows you to format numerical data in various ways.
For example, you can use it to convert a number to a specific format, format dates and times, or even round the number to a specific significant digit.
In this guide, we will explain how to use the TEXT function in Google Sheets. We will provide a few examples of how you can use it to manipulate text in your spreadsheet.
How to Convert a Number Into a Text Value in Google Sheets.
Read this guide to learn how we can use the TEXT function in Google Sheets to convert a number into a text value.
Step 1
Select a blank cell to place the TEXT function.
In this first example, we want to convert the number 42 into a currency format in cell C4.
Step 2
We can type the formula “=TEXT(B4,”$0.00”) to convert the value in B4 to a currency format that is appropriate for USD amounts.
The first argument of the TEXT function refers to the number you want to format. The second argument should be a string that specifies the user’s desired number formatting.
You might be wondering why we used zeroes in the format string.
Here’s why:
Adding a ‘0’ forces Google Sheets to display zeros if a number has fewer digits than the specified format.
In our example, this means that we will see numbers like $10.50 instead of $10.5. Similarly, the ‘0’ placed to the left of the decimal point prevents us from having an output like “$.25”.
Besides the ‘0’ digit, we can also use the ‘#’ character. However, unlike ‘0’, the ‘#’ symbol does not force the display of zeroes. For example, TEXT(11.1,”###.##”) outputs 11.1.
Step 3
Users can also specify whether they want the text to be formatted with commas after every three digits.
Step 4
We can use the format string “#,###.##, k” to convert numbers like 2567.5 to 2.57 k.
Step 5
The TEXT function can also be used to convert decimals into percentages.
Step 6
Another common use case for the TEXT function is formatting date values. The syntax of number formatting in Excel supports a variety of patterns.
For example, specifying “m” will return the month of the provided date as a 1-digit or 2-digit number.
If we specify “mmm”, the TEXT function will return the short name of the month ( such as “Jan” or “Dec”)
Users can also use “ddd” or “dddd” to return the date’s corresponding day of the week.
Step 7
Lastly, we can also use the TEXT function to format time values.
In the example above, we formatted the time to a 12hr format with the AM text on display.
Summary
This guide should be everything you need to use the TEXT function in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.