Text function in Google Sheets

What Is Text function in Google Sheets?

The TEXT function in Google Sheets is used to change a number into text. Besides numbers, you can also change dates, time, currency, and percentages. You can also specify the format in which you want the text to be displayed. The TEXT function is proper when you want to display numbers in a preferred format to make it more readable or combine the numbers with text. 

For example, we have entered 1.5 in cell A1. We must convert these numbers to dollars. Enter the formula shown below in cell B1.

=TEXT(A1,”$,0.00”)

You get the result as shown below. Thus, converting the numbers to text, date, and currency formatting, as well as even changing values to percentages, can be done quickly using the TEXT function in Google Sheets.

TEXT() Google Sheets Formula

The general syntax of the TEXT function is as follows:

=TEXT(number, format)

1. Number: It is a value, such as a date, number, or time, whose format you want to change
2. Format: The formatting to which you want to change the above number. They should be enclosed within the inserted format with quotation marks.

The format parameter uses the below two symbols for formatting. These are:

1. 0 – this parameter allows zeros to be displayed if the number has fewer digits than the format specified.
2. # – this input will work like 0, but it will not force the function to display zeros on either side of the decimal point.

Some date and time patterns supported by Google Sheets include:

1. d – day of the month as a single digit
2. dd – day of the month as two digits
3. ddd – day of the week displayed shortened.
4. dddd – displays the full name of the day.
5. m- for date, this displays the month in a single digit. For time, it displays minute singly.
6. mm – displays month or minutes in two digits, depending on date or time, respectively.
7. mmm – displays the short name of the month.
8. mmmm – displays full month name.
9. yy – shows the year in 2 digits.
10. yyyy – year in four digits
11. HH – hour in two digits in 24-hr format
12. hh – hour in two digits in 12-hour format
13. ss – displays the seconds.
14. AM/PM – shows the hours depending on the time in a 12-hour display.

How to Use TEXT Function in Google Sheets?

We can use the TEXT function in the following ways. Look at the sheet below.

Step 1: We have a decimal number. To convert it to a specific time format, go to any cell(B2 in this case) and type =TEXT(.

Step 2: Type the two arguments, the number you want to modify, and the format. The formula is as follows.

Step 3: Press Enter. You get the time in your desired format.

Entering through Google Sheets Menu

Step 1: You can choose to enter the formula through the Google sheets “Insert” tab. Click on “Insert” and go to “Function.”

Step 2: Go to the option in the selected list. Choose the “Text” function under “Text.”

Step 3: Now, you can enter the details required, as shown in the example above. Input a value whose formatting you want to change by manual input or cell reference. Define the formatting and insert the argument accordingly.

Examples

Let us look at some simple examples of how to use the TEXT function in Google sheets in different ways.

Example #1 – Prefix the Text Strings to the Newly Formatted Date values

Let us look at an example of how to prefix some text strings to date values that are formatted using the text function.

We can take the example of the present date. For this, we use the TODAY() function. Let us look at the output of the function.

Step 1: Type =TODAY() in cell B4.

You get the current date.

Step 2: Let us format it in a particular way using the TEXT function. Enter the formula below in cell B5.

=TEXT(TODAY(),”dd mmm”).

Step 3: We get the value as shown below.

Step 4: Now, to prefix it with a text string, we use CONCATENATE. Enter the formula shown below in cell B3.

=CONCATENATE(“Today’s date “,TEXT(TODAY(),”dd mmm”))

Step 5: Press Enter and see the result.

Example #2 – Join the Newly Formatted Time and Date Values

In this example, let us format the date and time in two separate cells.

Step 1: Enter the following formula in cell B2.

Step 2: Format the time as shown in cell B3.

Now, join these two values in cell B4. To do this, you may join the TEXTJOIN function.

Step 3: Now, you can join these two values.

=TEXTJOIN(“–“,TRUE,B2,B3).

The delimiter is two hyphens, while TRUE indicates the inclusion of empty cells. Press Enter to join the two formatted date and time values.

Example #3 – Extract a Mobile Number from its Scientific Notation

In this example, we have some numbers in their scientific notation. These are actually mobile numbers.

Step 1: To convert this to their original mobile numbers, use the following TEXT formula.

=TEXT(A2,”0”)

 Step 2: The “0” helps display the whole number. Press Enter.

Step 3: Drag the formula to cell B6. All the phone numbers are displayed.

We have now converted the number in the scientific notation in A2 into text in B2.

Example #4 – Prefix a Text String to the Initially Formatted Monetary Value

Step 1:  We have some values in Column A. We must format it in a particular way as currency. Use this formula in cell B2.

=TEXT(A2,”$####.00″). Press Enter and drag the formula to cell B4.

Step 2: Let us now add some text strings to precede the formatted values. Enter the following formula in cell C2.

=”The Total Amount: “&TEXT(A2,”$####.00”)

Here, we concatenate the text string and the formatted value using the &. It is used to display the value in dollars followed by two decimal points. Check out the result when you press Enter.

Now, drag the formula to cell C4.

Example #5 – Add leading zeros to numbers

Let us now look at how to add leading zeroes to numbers. Google Sheets normally automatically removes leading zeros typed before a number. However, what if you want to keep the preceding zeros? It is how we do it using the TEXT function. Look at the numbers below.

The TEXT function in Google Sheets adds numbers with leading zeros even if the values are not the same length. All you must do is use the format code containing only zeros, as shown below. Here, you give the number of zeros you want to display. We need an 8-digit number. Hence, enter the following formula in cell B2.

=TEXT(A1,”00000000″). Press Enter.

You can see how it has become an 8-digit number with preceding zeros. Thus, you have an 8-character long string. Now, drag the formula to cell B3 and check the result.

Example #6 – Converting Decimal to Percentage with TEXT Function

Using the TEXT function, you can easily convert a decimal into a percentage. Le t us enter a few decimals in Column A.

Step 1: Use the following formula in cell B1 to convert it into a decimal.

=TEXT(A1, “0.00%”). Press Enter. You get the percentage as shown below.

Step 2: Drag the formula till cell B3 to get the other percentages.

Example #7 – Converting Decimal to Fraction with TEXT Function

We have the following list of decimals in a Google sheet. Each decimal in column A should be converted to a fraction in column B. Hence, we can type the following formula into cell B2:

=TEXT(A2,”#???/???”)

We then drag and fill this formula into each of the remaining cells in column B.

Important Things To Note

1. If numbers require a specific format, such as leading zeros, you can use the TEXT function in Google Sheets to add them.
2. The TEXT function allows you to extract specific components of a date like day, month, and year.
3. The TEXT function creates a text string and not a number.
4. The format parameter of the TEXT function can’t have an asterisk (*) and a ? pattern.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the Text function in Google Sheets formula and examples. We can download the template here to use it instantly.

Recommended Articles

Guide to What Is Text function in Google Sheets. We explain how to use Text function in Google Sheets with examples. You can learn more from the following articles. –

Find and Replace in Google Sheets

INDEX MATCH Function In Google Sheets