What is Convert Numbers to Text in Google Sheets?
Google Sheets helps users store, organize, and work on data. One of the most common uses is converting numbers to text for our manipulations. This can be done in different ways. Some of them is using functions such as TO_TEXT and using the & operator. The TEXT function is another widely used function to convert numbers to text with additional formatting options. It is very useful when the numbers should be displayed in a specific format.
Let us look at a simple example using one of the methods. When using the TO_TEXT function, we get the numeric value as a string. Here, TO_TEXT(20) will return “20” as a text value. This functionality is helpful when dealing with data that includes both numerical and textual information.
How To Convert Numbers to Text in Google Sheets?
In this section, we look at different ways in which numbers can be converted to text in Google Sheets. There are various ways to convert numbers to text in Google Sheets. Here are the most common methods:
Using TEXT Function
The TEXT function is one of the most versatile ways to convert numbers to text in Google Sheets as it also allows formatting the numbers when converting them to text.
Syntax:
=TEXT(value, format)
- value: The number or reference to convert to text.
- format: The desired format for the number as text like percentage, currency etc.
Using TO_TEXT Function
The TO_TEXT() function helps convert numbers into text without a format. It is best for straightforward conversions without any specific formatting.
Syntax:
=TO_TEXT(value)
- value: The number or reference to convert to text.
Another method to convert a number to text is by concatenating it with an empty string which will automatically convert the number to text.
For example, if cell A1 contains 1234, you can use:
=A1 & “”.
Let us see a simple example of using one of these methods.
Step 1: We have a number 100 in cell A1. To test if it is a number. We use the ISNUMBER function in B1. Enter the following function in cell B1 and press Enter.
=ISNUMBER(A1).
Step 2: To convert it to text, use the following function in cell A2.
=TEXT(A1,”0.00″). Press Enter.
Step 3: You can see that the format is changed for the displayed text. Now, to check if it is a text, we use the formula
=ISTEXT(A2) in B2. Press Enter. You get TRUE. So, the number has been successfully converted to a text.
Examples
Let us look at the different ways in which we can convert numbers to text. We can go through the options one by one with different examples.
Example #1 – Convert Numbers to Text using TEXT Function
The TEXT function in Google Sheets is the most versatile way to convert numbers to text. The best part of using this function is that it allows you to format the numbers when we convert them to text.
Its syntax is as follows:
=TEXT(value, format)
- value: The number or reference you want to convert to text.
- format: The desired format for the number as text
We have a few cells containing some numbers from A1 to A3. Let us try to change them to text using the TEXT function. We can use different formats to test them.
Enter the following formula in cell B1.
=TEXT(A1, “$#,##0.00”)
Press Enter.
Step 2: For a different format, enter the following formula in cell B2.
=TEXT(A2, “0.00”).
Step 3: Similarly, for B3, enter the following formula.
=TEXT(A3, “0%”)
Check the result. You can see all the numbers have been converted to text. To cross-verify, we have used the ISTEXT function in Column C which has all been proven to be true.
Formats for Date example
=TEXT(A4, “mmm. dd, yyyy”)
Format for Time example:
=TEXT(A5,”hh:mm AM/PM”)
Example #2 – Convert Numbers to Text Using CONCATENATE Function
Let us use the CONCATENATE function in Google Sheets to convert numbers to text. We can do so by joining a number with an empty string to implicitly convert the number to text. The syntax of the CONCATENATE Function is as follows:
=CONCATENATE(string1, [string2, …])
string1, string2, …: The strings or values you want to concatenate.
To convert a number to text, concatenate the number with an empty string (“”).
Step 1: Cell A1 contains a number 1000. Enter the following formula in cell B1.
=CONCATENATE(A1, “”)
This will convert the number 1000 to a text value. This fact can be verified with the left orientation.
Step 2: One can also use CONCATENATE to add additional text.
For instance, in B2, enter
=CONCATENATE(“Amount:”, A1)
Example #3 – Convert Numbers to Text using Custom Number Formats Option
In this example, we have some numbers in cell A1 and A2.
Step 1: Select the cells that contain the numbers you want to convert to text.
Step 2: Go to the Format menu at the top of Google Sheets. Select Number -> Custom number format from the dropdown.
Step 3: In the Custom number format field, enter the @ symbol to display the number as text.
@
Apply the Custom Format. Click Apply to confirm the format.
The number will be displayed as a text.
We have verified it using the ISTEXT function in B1.
Example #4 – Convert Numbers To Text Using TO_TEXT Function
When you use iSTEXT, it preserves any existing formatting such as dates, decimals, percentages, etc. Here, the converted value will look exactly like the original number but will be treated as text by Google Sheets.
If cell A1 contains the value 11/10/2025, you can use:
=TO_TEXT(A1)
This will convert the number to text with the format retained.
Important Things To Note
- If you need to convert the text back to numbers again in Google Sheets, you can use the VALUE function.
- When dealing with large datasets, converting text to numbers cell by cell is time consuming. Hence, use ARRAYFORMULA to handle multiple cells at once.
- For a quick method to convert numbers to text without any functions, just add an apostrophe (‘) before the number. This tells Google Sheets to treat the number as text.
Frequently Asked Questions (FAQs)
Some of the limitations include:
The numbers obtained after converting from text behave differently compared to numeric values when used for additional purposes like filtering. Hence, we may have to convert them back for operations. If specific formatting is crucial as in the case of using currencies, we only have to use the TEXT function.
When we use complex formulas over large datasets, it may impact the performance and hence it is necessary to optimize the data wherever possible.
When converting numbers to strings, remember that errors may occur if the settings are different based on location. For example, some places use the decimal separator as a comma instead of a period.
Some of the different methods to convert numbers to text include:
1. TEXT function: For converting numbers to text with specific formatting.
2. TO_TEXT function: Easy way to convert numbers to text.
3. Concatenation (& “”): We can use this operator to convert a number to text without formatting.
4. ARRAYFORMULA: To convert an entire range of numbers to text.
One of the best ways to convert numbers to text with the formatting intact is using the Custom Number Formats option to display a number as text without changing its actual value.
1. Select the cells containing the numbers.
2. Go to Format > Number > Custom number format.
3. Enter @ in the custom number format box and click Apply.
Download Template
This article must help understand Convert Numbers to Text in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Convert Numbers to Text in Google Sheets. We learn how to convert numbers to text in different ways with examples. You can learn more from the following articles. –
Leave a Reply