Mehrab Imtiaz
In this article, we will show you how to convert number data to text values in Google Sheets.
Generally, it can be achieved using built-in functions like TO_TEXT and TEXT, the latter being the most versatile. However, we will also show a special add-on function that can prove to be great in a slightly different scenario.
Let’s get started.
3 Ways to Convert Number to text in Google Sheets
There are multiple number formats that are recognized in Google Sheets: basic numbers, date, time, percentage, and currency are the most common.
So, if we input these values in a Google Sheets worksheet, the data will be right-aligned by default:
On the other hand, text data are left-aligned in a cell, making it quite easy to distinguish between text and numerical data in the application.
1. Using the TO_TEXT Function
The TO_TEXT function provides the simplest way to convert number values to text in Google Sheets.
TO_TEXT function syntax:
TO_TEXT(value)
This function takes a value or a reference to a value and converts it to a text string.
=TO_TEXT(B2)
And that is all it does, convert number data to text. But if a text value is passed through the function, then it remains unchanged.
2. Using the TEXT Function
Next, we have the TEXT function. This function takes things one step further from the TO_TEXT function by including a field for formatting the output. We can see this in the function’s syntax:
TEXT(number, format)
The format field is where you input the format code you want to convert the value to. This is input inside quotation marks (“”) since it’s a text.
Let’s see what happens when we input the same format for the different types of numbers with TEXT:
=TEXT(B2,"##.00")
While the numbers were successfully converted to text, the format that was used is uniform and, in most cases, it doesn’t make sense. Especially for date and time.
So, inputting the right format is crucial when using the TEXT function to convert number values to text strings in Google Sheets.
The format we’ve shown above, “##.00”, converts numbers to text and shows 2 decimal places as represented by 0s after the dot (.).
The 0 also makes the format include any insignificant zeros in the number. Whereas, the hash/pound (#) symbol does not.
Here are some decimal format conversion examples:
In Google Sheets, dates and times have their own unique formats. This means that converting them to text will require different formatting rules. Let’s start with converting time to different formats time first.
To convert to the 24-hour format:
=TEXT(time,”hh:mm:ss”)For the AM/PM format:
=TEXT(time,”hh:mm:ss AM/PM”)
Learn More: Google Sheets: Convert Time to Number (3 Easy Ways)
In our opinion, the best use of the TEXT function is when we use it to convert date values to a text string in Google Sheets.
With text, there are multiple formats that can be applied to output a date.
Here is a table showing some format codes that you can use:
| Date Format Code | Result |
|---|---|
| dd-mm-yyyy | 25-2-2022 |
| mm/dd/yyyy | 2/25/2022 |
| MMMM d, YYYY | February 2, 2022 |
| dd-MMM-YYYY | 02-Feb-2022 |
| MMM d, YYYY | Feb 2, 2022 |
| mmm-yy | Feb-22 |
| dd MMM YYYY | 02 Feb 2022 |
| dddd | Wednesday |
| ddd, MMMM DD YYYYY | Wed, February 02 2022 |
3. Using the NUMBERTEXT Function Addon to Convert Number to Words in Google Sheets
Let’s change the scenario up a little bit and look to convert numerical values not simply into text but name them in words in Google Sheets. For example, convert the number 100 to the word one hundred.
So far, there’s no built-in feature or function in Google Sheets that allows us to do so. So, what can we do about it other than inputting the values manually?
Simple, install a free add-on. An add-on called NUMBERTEXT.
Let’s guide you through the whole process step by step:
Step 1: Navigate to the Extensions tab at the top of the Google Sheets window. Here you’ll find the Add-ons tab. Then select Get add-ons.
Extensions > Add-ons > Get add-ons
Step 2: In the Marketplace window that opens up, type in the keyword “numbertext” in the search bar. Then navigate to the add-on page.
Step 3: Proceed to Install the add-on to your account.
Give permission if necessary. Simply click on the Allow button when it pops up.
Step 4: With the installation now complete, you can apply the NUMBERTEXT function the same way as you would any other function in Google Sheets. Type =NUMBERTEXT in any cell and it will pop up.
Step 5: Simply pass a number or a reference to a number through the function to find its word equivalent.
=NUMBERTEXT(B2)
Note: Since NUMBERTEXT is an external function, it may take a couple of seconds to process.
Final Words
That concludes all the ways we can use to convert number values to text strings in Google Sheets. Doing so is quite simple since we have great built-in and additional features to help us.
Feel free to leave any queries or advice you might have for us in the comments section below.
