[ad_1]
You can format the data in your cells in different ways for currency, dates, percentage, text, and numbers. If you’re working with formulas where you want to format those results, you can use a handful of Google Sheets functions.
These functions work with other functions and formulas, but they also work alone. However you decide to use them, we’ll show you the syntax and arguments you need.
Format as dollars: TO_DOLLARS
When you want to convert data to dollars, you can use the TO_DOLLARS function. the syntax is TO_DOLLARS(value) with just that required argument. You can use a cell reference or an inserted value.
The function is equivalent to using Format > Number > Dollars in the menu.
Note: This feature does not convert other currencies to US dollars. Instead, you can use the GOOGLEFINANCE function for current exchange rates.
To convert a value in a cell to dollars, insert the cell reference into the formula:
=TO_DOLLARS(A1)
To convert an amount, just plug the number into the formula:
=TO_DOLLARS(57.29)
To add numbers in a cell range and convert the result to dollars, use this formula:
=TO_DOLLARS(SUM(A1:A5))
Format as text: TO_TEXT
Maybe you have a number that you want to convert to text. Here, you can use the TO_TEXT function. the syntax is TO_TEXT(value) where you can use a cell reference or a number for the argument.
The function is equivalent to placing an apostrophe in front of the number.
To convert a number to a cell, insert the cell reference into the formula:
=TO_TEXT(A1)
To convert a specific number, plug it into the formula:
=TO_TEXT(5.99)
Format as a percentage: TO_PERCENT
When you calculate a percentage in Google Sheets, you may want to format it as such. This is where TO_PERCENT comes in handy. the syntax is TO_PERCENT(value) where you can use a cell reference or a number for the argument.
The function is equivalent to Format > Number > Percent in the menu.
To convert a value to a cell, insert the cell reference into the formula:
=TO_PERCENT(A1)
To convert a certain value, include it in the formula:
=TO_PERCENT(0.123)
To calculate the percentage and format it as such, you can use the following formula. This calculates the percentage of part in cell A1 of the total in cell B1 and formats the result.
=TO_PERCENT(A1/B1)
Format as date: TO_DATE
Have you ever tried to enter a date into a cell and ended up with a number instead? For example, you can type 09/22/2022 and end with 44826.
RELATED: How to create a custom date or time format in Google Spreadsheets
The TO_DATE function converts that number to the date you need. the syntax is TO_DATE(value) where you can use a cell reference or a number for the argument.
The function is equivalent to Format > Number > Date in the menu and formats the result as MM/DD/YYYY.
To convert a value to a cell, insert the cell reference into the formula:
=TO_DATE(A1)
To convert a specific value, just plug it into the formula:
=TO_DATE(44826)
Format as a number: TO_PURE_NUMBER
On the opposite side of the format, you may want to take your dollar, text, percentage, or date and convert it to a pure number. To do this, use the TO_PURE_NUMBER function. the syntax is TO_PURE_NUMBER(value) where you can use a cell reference or a formatted value for the argument.
RELATED: How to change and create a custom number format in Google Sheets
The function is similar to Format > Number > Normal only without any formatting, such as a comma for thousands.
To convert the date in cell A1 to a pure number, you would use this formula:
=TO_PURE_NUMBER(A1)
To convert a percentage to a number, plug it into the formula:
=TO_PURE_NUMBER(25%)
To add dollar amounts to a range of cells and format the result as a pure number, you would use this formula:
=TO_PURE_NUMBER(SUM(A1:A5))
The next time you need to convert or format a specific number or text, or the results of a formula, consider these Google Sheets features.
RELATED: 9 Basic Google Sheets Features You Should Know
[ad_2]
