Format a Number with a Formula in Google Sheets

To format a number with a formula in Google Sheets can be crucial as many users can have different requirements and formatting styles in mind. These small but important customizations can only be done with formulas.

To add to that, we can apply these formatting formulas either by directly using functions in the worksheet cells or by generating a format using a formula in the Custom Number Formatting window.

Let’s have a look at these approaches with a few examples.

How to Format a Number with a Formula in Google Sheets

Couple Basic Examples for Formatting Numbers with a Function

We have a function made specifically to format numbers in Google Sheets which is unironically called TEXT, and its official syntax proves that:

TEXT(number, format)

As long as a user knows the formatting symbols and applications, and how they work in Google Sheets, you can easily format any number to the desired requirement.

The biggest advantage of using the TEXT function is that you can present the results in a separate cell while keeping the source values untouched.

1. Adding Decimal Points

A common example would be to add decimal places to a number.

The formula is quite simple:


format number with formula to add decimal places in google sheets

The # (hash) in the format field represents the base number. However, # does not count insignificant zeros (0). So, if the base number was 0, it would not have shown up.

On the other hand, .00 represents two decimal places. We can increase or decrease the number of zeros after the decimal point to change the number of decimal places.

The zero (0) in the format field will count any zero values, even insignificant ones.

2. Include Separators and Symbols

Some other common number formats would be to include separators and symbols.

A simple use of this is for formatting phone numbers.

We know that phone numbers can contain a plus (+) symbol with area codes separated by whitespace or dash (-).

Essentially, we are trying to format a number like this: 2550468753, to this: +(255) 0468753.

With the TEXT function, it can be as easy to simply add the symbols and separators inside the format field:

=TEXT(B2,"+(###) #######")

format number with symbol and separator using formula in google sheets

All the individual digits are represented by # (hash). All you have to do then is to place the symbols and separators in their respective position.

Note that Google Sheets reads numbers from the right to left when formatting. You can test it out by working with different numbers of digits and placing a separator between them.

Format Number as Currency with a Formula in Google Sheets

Currencies are also symbols that can be added to numerical values in Google Sheets.

By default, the application only allows the addition of the USD ($) symbol from its menu.

However, with TEXT, we can format other currency symbols to the number values in Google Sheets.

For example, let’s add the Euro (€) symbol to the numbers:


formatting a number with the currency symbol in google sheets

We can also replace existing currency symbols with new ones using the same formula:

the formula can also replace existing currency symbols

Though it doesn’t mean much, changing currency symbols without changing values. The fact is that you can. However, it can come in handy to correct mistakes before presenting the data.

Format Number as Date with a Formula in Google Sheets

If you didn’t already know, dates are considered to be numerical values in Google Sheets. That means that every single date has its own unique value.

This also means that we can format the date using the TEXT function.

The most common use is to change the date formatting, for example, changing an mm/dd/yyyy date to dd/mm/yyyy format.


changing the date value with formula in google sheets

Each value of the date is represented by a letter in the formatting:

  • Day: d
  • Month: m
  • Year: y

And depending on the combination and the number of letters you use, you can get a multitude of different date formats.

Date Format CodeResult
MMMM d, YYYYFebruary 2, 2022
MMM d, YYYYFeb 2, 2022
dd MMM YYYY02 Feb 2022
ddd, MMMM DD YYYYYWed, February 02 2022

the different formatting formulas for dates

Learn More: How to Format Date in Google Sheets (3 Easy Ways)

Custom Number Formatting in Google Sheets

Using the custom number format option is the way to go if you want to update the format of a number in the cell itself.

Note that this option will change the formatting of the source values, unlike when using the TEXT function.

To access this feature, simply select the cells you want to format and navigate to it from either the More formats icon:

navigating to custom number format option from more formats icon

Or the Format tab:

Format > Number > Custom number format

navigating to the custom number format option from the format tab of google sheets

Here, in the Custom Number Formats window, you can find the field where you can enter your custom number format formula in Google Sheets.

the custom number formats window

1. Format a Phone Number

For the first example, let’s try to format a number as a phone number with a formula.

The custom number formula for this format will be:

"+("###")"" "#######

phone number custom format formula in google sheets

The result:

formatting phone numbers with custom number formula

Does the formula look familiar?

Yes, it is the same format formula we used in the format field of the TEXT function previously.

The first difference here is that the text aspects (symbols and whitespaces) need to be enclosed in quotation marks (“”).

The second difference, perhaps the most important one, is that the values remain numerical.

So, this means that if a formatting formula works for one method, it should work for the other.

2. Change Color of Numbers (Conditional Format)

Before we move to color the numbers, we must first understand how Google Sheets views primary data.

It can be divided into four parts:

  1. Positive
  2. Negative
  3. Zero
  4. Text

These data can be accessed from the Custom Number Formats window:

the four data for values in google sheets

The “General” keyword represents the default value of the selected number or numbers.

Whereas, an “@” is used for the Text data to keep any text values in the selection intact.

All of these data are separated by a semicolon (;).

Now, to format any number, we must update the respective data slots.

For example, to format the color of all negative numbers, we must add the color name to the data slot. Applying the color Red for negative numbers:


custom number format formula changing the color of the negative numbers to red

The change will be reflected in the preview.

The result:

format number to change color with negative value with formula in google sheets

3. Replace Thousands Number Value with K

For our final example, we have another practical scenario where a user needs to replace the thousand number value with K.

We all know that a thousand can sometimes be represented as K. We are trying to apply that number format using the custom number format in Google Sheets.

First, we will apply the custom formula “0,”

inputting 0, to set a place holder for thousands value point

This will make the worksheet ignore the last 3 digits of the number or the thousands digits.

Next, we will add the text K in quotation marks, making the formula:


custom number formula to replace thousand value with k

The result:

replacing thousands with k in google sheets using custom number format

Final Words

We can take a couple of approaches if we want to format a number with a formula in Google Sheets.

The first is with the TEXT function for when you want the source data to remain untouched.

The second is using the custom number formatting feature of Google Sheets when you want a permanent update to the number values.

Whichever way you choose, the fundamental formula for formatting remains mostly unchanged.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply