Numbers are an essential part of many data flows. As you start building different Zaps, sooner or later, you’ll need to manipulate numerical data. Typically, such an operation would require you to use code. But, thanks to the Numbers step, you can edit numerical data right inside Zapier.
In this article, we’ll explore all the different things that you can do with numerical data using the Numbers event inside Formatter by Zapier.
When to Use the Numbers Step Inside Zapier Formatter
The most common use case for Zapier Formatter is to change the format of the number itself. This comes in handy for example when you need to pull data from various data sources into one analytics tool or spreadsheet.
This is important because different countries or regions format numbers in different ways. For example, they may use a period or a comma for decimals, and use either of them (or space) for grouping. Zapier helps you change all numbers, no matter the formatting, into one single format.
The same problem arises whenever you’re editing currency data or phone numbers.
In the case of currency data, you can change both the grouping, as well as the position of the currency symbol itself. Note that some countries put the currency symbol in front while others put it behind the number.
The phone numbers option is critical whenever you need to ensure that the data is correct and unified before adding it to another tool. For example, the step can be used to clear and unify data that you’re sending to a client spreadsheet or CRM.
Similarly, whenever you need to perform a calculation, there are two steps you can use. You can choose the perform math operation or spreadsheet-style formula steps. This comes in handy whenever you need to perform a calculation on different data inputs before you send that data to its final destination.
Lastly, you can use this step to generate a random number. This is useful, especially whenever you need to create a simulation or draw a random number.
Of course, in reality, there are thousands of use cases for each of the above steps. Now that you know when you might use them, let’s look at how you can do that.
How To Use Numbers by Zapier Formatter
To format numbers you first need to select “Numbers” in the Zapier Formatter action step:
Compared to date/time, the Numbers step has more available transformations – six current and one legacy:
The available transformations include:
- Formatting currency
- Formatting a number
- Formatting a phone number
- Performing a math operation (which also has one legacy transformation)
- Finding a random number
- Using a spreadsheet-style math formula
Let’s look at how you can use each – starting with formatting currency.
How to Format Currency in Zapier
First, select “Format currency” from the drop-down list of transformations for the Numbers action step:
In our case, we want to format 12525.75 to US dollars – in the exact way that you’d write it down in the US.
Keep in mind that certain countries write down higher numbers and currencies in different ways than others. Zapier allows you to select from several locations, as well as formats.
To set the Zap correctly, you need to:
- Select the input Formatter data. In our case, it’ll come from a Spreadsheet
- Select the desired currency. Here: US Dollar
- Select Locale (for example, the US, UK, or Russia). This step impacts how you receive the output number.
- Select the currency format – this gives you further customization options as to how the output will look like.
In our case, we want to get $12,525.75. This is how to set up the zap to get there:
And here’s the output:
Adding Space, Periods, and Commas for Grouping and Decimals in Zapier
The next transformation, called “Format Number” helps you put the space, periods, and commas in the right place in a number. This is an important transformation because different formats may be used in different countries.
As a result, failing to format them may lead to misunderstandings or data errors. The latter is critical if the app that you want to send the data to accepts numbers only in a specific format. So, how to use the Format Number option?
Let’s stick to the 12525.75 that we used in the previous example. In some countries, instead of a period, you want to use a comma for decimal numbers. Similarly, you’d use a period for grouping. Thankfully, doing that with Zapier is as easy as 1-2-3!
- Pick your input data. In our case, the data comes from a Google Spreadsheet.
- Select the decimal mark for input. In our case, that’s a period.
- In the “To Format”, we want to put “Period for grouping & comma for decimal.”
And the output is 1.252,75 – which is a different format than that used in the US, but it’s still correct!
Tip: Whenever formatting a number using this step, make sure that you set a correct input decimal mark. If you don’t, Zapier might misunderstand your input number and return an incorrect output.
How to Format a Phone Number in Zapier
Next, let’s look at how you can format a phone number in Zapier. To do that, you need to select the “Format Phone Number” option from the drop-down list of available transformations:
The available transformations include both national and international formats. You can select those with or without parenthesis, hyphens, and the country code.
If you pick an international phone number, you can set Zapier to validate it against a preselected country. Please note that you can only have one validation country set at once (Zapier can’t detect it on its own).
However, you can change it dynamically if you import the country ISO code together with the phone number in the trigger step.
There are several ways in which you could set a dynamic country code in Zapier. The easiest way is to add a country code to your CRM and search using the provided phone number for that code.
Let’s say that you’re using Airtable to manage your data. In this case, you want to have two columns: Phone Number and Country Code.
Then, whenever you add a new record, you can pull both details from the same trigger.
Here’s how it’d look like in practice. In our case, we did the following:
- Pulled the phone number from Airtable. 48123456789 is a dummy phone number for Poland.
- We set the format to +1 555 800 1212. That way we can get the “+” sign without the hyphens.
- We also added a country code and set Zapier to validate our phone number. As you can see, we pulled the country code from Airtable:
The problem with this result is that it isn’t necessarily formatted correctly. Yes, it is correct for a landline phone number. But, if it was a 9-digit mobile phone number, the right format would be +48 123 456 789.
However, if all you need is to add the + sign (and or/remove the plus sign/country code), you can always use one of the steps that don’t add spaces or hyphens.
For example, for the above phone number, we’d use the E164 format, which is the international standard for formatting phone numbers. And here’s the output:
As you can see, the above format is perfect if all you need is to ensure that the phone number has the + sign and a valid country code.
How to Perform a Math Operation in Zapier
Now, let’s find out how you can perform a math operation. Please note that the available operations are pretty basic and include:
- Making the number negative
Please note that this step shouldn’t be used on line-items or if you’re looking for more advanced calculations. If that’s the case, you want to use a spreadsheet-style formula instead.
When setting this step up, there are two ways you can either set both input values to be generated dynamically or keep one (or more) of them constant.
In our quick Zap test, we’ll set both to be the former. Let’s look at what happens if you divide 531,441 by 9:
And here’s the result:
Of course, dividing a number by its row ID doesn’t make much sense in real life. But, it’s a good way to show you how this step works.
How to Generate a Random Number in Zapier
This step is the most straightforward of all as it doesn’t require any input. All you have to do is select the highest and lowest possible number that you want to generate.
Then, set decimal points for the number and you’re good to go:
And, here’s the random output:
While this Zap may sound simple, there are dozens of use cases for it.
Of course, as it’s completely random… so it may be close to impossible to generate it again for that range! Or, the same number may appear the next time you test your trigger. You only have one way to check it out – sign up for Zapier and test the step yourself!
How to Use Spreadsheet-Like Formulas in Zapier
The last step is the most advanced one. There are almost 100 different formulas that you could use to format numbers. You can find a list of all formula functions on this page.
Of course, we don’t have enough room to go through all the formulas here. Instead, let’s look at the general concept of how you can format numbers with spreadsheet-like formulas in Zapier Formatter.
First, let’s find the formula that you want to use. In my example, I’ll use the SUM formula.
As you can see, this Zap is very similar to a spreadsheet. All you need is to enter the formula and then the values that you want to use in that formula.
Here’s what a setup with random numbers generated from a Google sheets row looks like:
And here’s the output for the above formula:
The great thing about this Zap is that you can even set the operation itself to be dynamic. In the below example, we pulled the name of the operation together with the numbers used in it:
Obviously, it doesn’t make much sense to pull data from a spreadsheet when you can just perform the same operation there.
But, if you were to pull that same data from a CRM or any other sales or marketing tool, Zapier can save you lots of time. Not to mention all the headaches of trying to perform calculations manually.
Get the Most of Your Numerical Data with Zapier
Numbers play a vital part at every stage of marketing, sales, or management workflows.
However, numbers are not the only thing you can transform using Zapier.
Other things you can play with include dates, time, and text. There’s also a thing called “Utilities” which allows you to perform all kinds of cool data manipulations.
- Introduction to Zapier Formatter
- Guide to manipulating date and time in Zapier
- Guide to manipulating text in Zapier
- Guide to Zapier Formatter Utilities
And, if you need any help with data manipulation yourself, don’t hesitate to reach out. Visit this page to send a message or schedule a discovery call and let’s discuss your automation setup.
Hey, I’m Jacek. I’m the founder and Chief Automator at Clickleo.com
I’m on a mission to help you use automation to reclaim your time and achieve more in your business.
You can find out more about me – and why I started Clickleo – over on this page