Published on October 31, 2023 by Jake Sheridan
In this tutorial, you will discover how to use the powerful TEXT function in Google Sheets.
A powerful function for data formatting
The TEXT function in Google Sheets is a versatile and powerful tool that allows you to format numerical data in various ways.
For example, you can use it to convert a number into a specific format, format dates and times, or round a number to a specific decimal place.
In this guide, we will show you how to use the TEXT function in Google Sheets. We will provide you with some examples of how you can manipulate text in your spreadsheet.
How to convert a number to text value in Google Sheets
In this first example, let’s convert the number 42 into a currency format in cell C4.
- Select an empty cell to place the TEXT function.
- Enter the formula “=TEXT(B4,”$0.00″)” to convert the value in B4 into a currency format suitable for US dollars.
The main argument of the TEXT function refers to the number you want to format. The second argument should be a string that specifies the desired format.
You may be wondering why we used zeros 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 “0.25 $”.
In addition to the ‘0’ digit, we can also use the ‘#’ symbol in the format string. However, unlike ‘0’, the ‘#’ symbol does not force the display of zeros. For example, TEXT(11.1,”###,##”) displays 11.1.
Other uses of the TEXT function
The TEXT function can also be used to convert decimals into percentages, format date and time values, and much more.
To convert a number to a percentage, use the appropriate format string as shown in the following example:
The TEXT function can also format date and time values. For example, by specifying “m”, you will get the month of the given date as a one or two-digit number. If you specify “mmm”, the TEXT function will display the short name of the month (e.g., “jan” or “dec”).
You can also use “ddd” or “dddd” to get the corresponding day of the week for the date.
Lastly, you can use the TEXT function to format time values.
For example, in the above example, we formatted the time in a 12-hour format with the display of the text “AM”.
Conclusion
This guide should provide you with everything you need to know about using the TEXT function in Google Sheets. Feel free to create a copy of this sample spreadsheet to try it out for yourself.
Discover more tips and tutorials on Crawlan.com to become an expert in Google Sheets!
