Formatter by Zapier
Formatter by Zapier Help & Support
- How to Get Started with Formatter by Zapier
- How to use Formatter Functions
- How to use Line-items with Formatter
- How to Import CSV Files with Formatter
- Common Problems with Formatter
How to Get Started with Formatter by Zapier
About Formatter by Zapier's Integration#
- Admin/owner permissions: Zapier account
- Paid/versioned account: Paid Zapier subscription to use multi-step Zaps
- Custom field support: N/A
- Blog post: https://zapier.com/blog/zapier-formatter-guide/
Connecting with Formatter by Zapier#
To get started with Formatter, first add a trigger to your Zap, then add the Formatter action and select the category of data you wish to tweak:
Zapier will then show you a dropdown where you select one of the available operations or transforms to perform.
After selecting a transform, choose the field from your Trigger app you wish to transform, then fill in any additional options that may appear depending on the option you chose. You can test your step to verify that the action is working as expected, then add another action app to use the formatted data.
Here's details on the options with each tool in Formatter:
Formatter can clean up text and make it look perfect. Here's the tricks it can perform—and you can add multiple Formatter actions to, say, remove HTML and truncate text at the same time.
- Capitalize: Capitalize the first character of every word.
- Lowercase: Make every character in the text lower case.
- Uppercase: Capitalize every character in a text field.
- Titlecase: Capitalize the first character of every word, except for those with fewer than 4 characters.
- Pluralize: Pluralize any English word (eg. frog turns into frogs; child turns into children).
- Replace: Replace any character, word or phrase in the text with another word or phrase.
Extracting and Structuring Text#
- Remove HTML Tags: Remove every HTML tag to leave just the plain text.
- Extract Email Address: Find and copy an email address out of a text field.
- Extract Phone Number: Find and copy any phone number (including area and country codes) out of a text field.
- Extract URL: Find and copy a URL out of a text field.
- Extract Number: Find and copy any number out of a text field.
- Extract Pattern: Find the first match for a regular expression in a text field. Returns all matched groups with start and end position.
Encoding and Decoding Text#
- URL Encode: Encodes text for use in URLs.
- URL Decode: Decodes text from URLs.
- Convert Markdown to HTML: Convert Markdown formatted text into HTML. Supports full GitHub-style Markdown syntax.
- Split Text: Split your content at a specified separator (e.g., a comma). You can use
[:newline:]to split text separated by line breaks.
- Trim Whitespace: Remove extra spaces at the end of the text.
- Length: Count the number of characters in a field.
- Word Count: Count the number of words in a text field.
Other Text Transforms#
- Convert to ASCII: Replaces all non-ASCII or special characters in a text field with normal characters.
- Default Value: Return a default value if the text is empty.
- Find: Find a set value in a field and optionally set a default value if nothing is found.
- Superhero Name: Convert a name into the name of a Superhero.
- Truncate: Limit your text to a specific character length, and delete anything over that.
Sometimes you want to find/replace or split on special characters. For these cases, you can use a special character syntax:
[:space:]- matches space (
[:tab:]- matches tab (
[:newline:]- matches newline (
[:return:]- matches carriage-return (
Numbers may not need any conversion to work correctly in your app, but they might need formatting to help them look nicer—and some automated math might make your job easier. Formatter includes these number tools:
- Format Currency: Format a number into a specific currency, locale, and format style.
- Format Number: Choose the symbol used for grouping and decimal place in a number. Does not perform any rounding or padding of the number.
- Format Phone Number: Format a phone number into a specific style.
- Perform Math Operation: Add, subtract, multiply, or divide values, or make a number negative (Does not support Line-item fields)
- Spreadsheet-Style Formula: Evaluate a formula similar to formulas found in popular spreadsheet applications.
With math operations, choose the fields you wish to add, subtract, multiply, or divide, and Formatter will perform the operation without any extra input. For more advanced math, try using Zapier's Code integration.
Spreadsheet-Style Formula allows you to evaluate a formula with the same syntax found in popular spreadsheet applications, like Excel, Numbers, and Google Sheets. Your formulas have the ability to perform arithmetic operations, make comparisons, and call a set of functions.
Please note, if you are trying to evaluate data that is text, and not numbers, you will need to wrap any and all text strings in "double quotes" otherwise the formatter will return with an error.
(1 + 2) / 4returns
IF(ISEVEN(2), 100, 200)returns
ROUNDUP(1234.567 * 106%, 2)returns
IF(1 < 2, IF(3 = 3, "you did it!", 20), 30)returns
you did it!
100 * (VALUE("123" & "." & "01") + 7)returns
- Returns the absolute value of a number
- Returns the inverse cosine of a number
- Returns the inverse hyperbolic cosine of a number
- AND(condition, [condition, ...])
- Returns TRUE if all of the conditions evaluate to TRUE, otherwise FALSE
- Returns the inverse sine of a number
- Returns the inverse hyperbolic sine of a number
- Returns the inverse tangent of a number
- ATAN2(x, y)
- Returns the inverse tangent of a pair of x and y coordinates
- Returns the inverse hyperbolic tangent of a number
- AVERAGE(number, [number, ...])
- Returns the average of a list of numbers
- CEILING(number, [factor])
- Returns a number rounded up to the nearest multiple of factor (e.g.,
CEILING(3.14159, 0.1) = 3.2and
CEILING(3.14159, 2) = 4)
- Returns the cosine of a number
- Returns the hyperbolic cosine of a number
- Returns radians converted to a value in degrees
- Rounds a number up to the next even number
- EXP(number, power)
- Returns the constant e raised to a power
- Returns the factorial of a number
- Returns the double factorial of a number
- Returns the logical value FALSE
- FLOOR(number, [factor])
- Returns a number rounded down to the nearest multiple of factor (e.g.,
FLOOR(3.14159, 0.1) = 3.1and
FLOOR(3.14159, 2) = 2)
- GCD(number, number, [number, ...])
- Returns the greatest common divisor of two or more numbers
- GEOMEAN(number, [number, ...])
- Returns the geometric mean of a list of numbers
- IF(condition, true_value, [false_value])
- Returns the true_value if the condition is TRUE or false_value if the condition is FALSE
- Returns a number rounded down to the previous integer
- Returns TRUE if a value is blank (empty); Otherwise, returns FALSE
- Returns TRUE if a value is an even number; Otherwise, returns FALSE.
- Returns TRUE if a value is a logical value; Otherwise, returns FALSE
- Returns TRUE if a value is NOT text; Otherwise, returns FALSE
- Returns TRUE if a value is a number; Otherwise, returns FALSE
- Returns TRUE if a value is an odd number; Otherwise, returns FALSE
- Returns TRUE if a value is text; Otherwise, returns FALSE
- LCM(number, number, [number, ...])
- Returns the least common multiple of two or more numbers
- Returns the natural logarithm of a number
- LOG(number, base)
- Returns the logarithm of a number, to a specified base
- Returns the base 10 logarithm of a number
- MAX(number, [number, ...])
- Returns the largest value from a list of numbers
- MEDIAN(number, [number, ...])
- Returns the median (the middle value) of a list of numbers
- MIN(number, [number, ...])
- Returns the smallest value from a list of numbers
- MOD(dividend, divisor)
- Returns the remainder from a division between two numbers
- MODE(number, [number, ...])
- Returns the mode (the most frequently occurring value) of a list of numbers
- Returns a logical value that is the opposite of a condition (ie. returns FALSE if condition is TRUE and returns TRUE if condition is FALSE)
- Returns a number rounded up to the next odd number
- OR(condition, [condition, ...])
- Returns TRUE if any of the conditions evaluate to TRUE, otherwise FALSE
- Returns the constant value of Pi
- POW(number, power)
- Returns the result of a number raised to a power
- POWER(number, power)
- Returns the result of a number raised to a power
- PRODUCT(number, [number, ...])
- Returns the product of a list of numbers
- QUOTIENT(dividend, divisor)
- Returns the integer portion of a division between two numbers
- Returns degrees converted to a value in radians
- Returns a random number between 0 and 1
- RANDBETWEEN(min, max)
- Returns a random number between two integers
- ROUND(number, [places])
- Returns a number rounded up or down (following standard rules) to a certain number of decimal places
- ROUNDDOWN(number, [places])
- Returns a number rounded up to a certain number of decimal places
- ROUNDUP(number, [places])
- Returns a number rounded up to a certain number of decimal places
- Returns the sign (+, -, 0) of a number
- Returns the sine of a number
- Returns the hyperbolic sine of a number
- Returns the positive square root of a number
- Returns the square root of a number multiplied by the constant Pi
- SUM(number, [number, ...])
- Returns the sum of a list of numbers
- Returns the tangent of a number
- Returns the hyperbolic tangent of a number
- Returns the logical value TRUE
- TRUNC(number, [places])
- Returns a number truncated to a certain number of significant digits by omitting less significant digits
- Returns the text converted to a numeric value
- Negation Operator
-Aconverts A to a negative number
- Percent Operator
A%converts A to a percentage value by dividing it by 100
- Exponentiation Operator
A ^ Braises A number to the power of B
- Multiplication Operator
A * Bmultiplies A by B
- Division Operator
A / Bdivides A by B
- Addition Operator
A + Badds B to A
- Subtraction Operator
A - Bsubtracts B from A
- Concatenation Operator
"A" & "B"concatenates the text value A and B into a single text value
- Equal Condition
A = Bis TRUE when A is equal to B
- Not Equal Condition
A <> Bis TRUE when A is not equal to B
- Greater Than Condition
A > Bis TRUE when A is greater than B
- Less Than Condition
A < Bis TRUE when A is less than B
- Greater Than or Equal Condition
A >= Bis TRUE when A is greater than or equal to B
- Less Than or Equal Condition
A <= Bis TRUE when A is less than or equal to B
Dates can be tricky when moving them between apps, since each app has its own way of listing dates and times. These actions can help you get them in the format you need—and can let you add or subtract time to account for time zones and more.
Format - Change the format and time zone of a timestamp#
To switch a timestamp to a different format or time zone from the one sent on, choose the Format utility from the dropdown. Then, insert the timestamp from the Trigger app, and select the format the date is in currently along with the new format you want it to be in.
If the format you want isn't in the drop-down options no problem, you can choose a custom value here and create your own by typing it in.
You choose the format by referencing the table below:
Date/Time Custom Value Table#
|Year||YYYY||2000, 2001, 2002 ... 2012, 2013|
|YY||00, 01, 02 ... 12, 13|
|Month||MMMM||January, February, March ...|
|MMM||Jan, Feb, Mar ...|
|MM||01, 02, 03 ... 11, 12|
|M||1, 2, 3 ... 11, 12|
|Day of Year||DDDD||001, 002, 003 ... 364, 365|
|DDD||1, 2, 3 ... 364, 365|
|Day of Month||DD||01, 02, 03 ... 30, 31|
|D||1, 2, 3 ... 30, 31|
|Day of Week||dddd||Monday, Tuesday, Wednesday ...|
|ddd||Mon, Tue, Wed ...|
|d||1, 2, 3 ... 6, 7|
|Hour||HH||00, 01, 02 ... 23, 24|
|H||0, 1, 2 ... 23, 24|
|hh||01, 02, 03 ... 11, 12|
|h||1, 2, 3 ... 11, 12|
|AM / PM||A||AM, PM|
|Minute||mm||00, 01, 02 ... 58, 59|
|m||0, 1, 2 ... 58, 59|
|Second||ss||00, 01, 02 ... 58, 59|
|s||0, 1, 2 ... 58, 59|
|Sub-second||SSS||000, 001, 002 ... 998, 999|
|SS||00, 01, 02 ... 98, 99|
|S||0, 1, 2 ... 8, 9|
|Timezone||ZZ||-07:00, -06:00 ... +06:00, +07:00|
|Z||-0700, -0600 ... +0600, +0700|
Add/Subtract Time - Manipulate a date and/or time by adding/subtracting time.#
To adjust a date/time by a specific amount, you need to supply
<+/-> <amount as a number> <unit>. Unit can be "years", "months", "weeks", "days", "hours", "minutes", or "seconds". Here are some examples:
+5 years +2 months -3 minutes
Note that this syntax is different from the syntax you would use to modify a date/time in a date/time field. The examples above only apply to the Add/Subtract Time utility in the Formatter action.
Formatter includes some extra tools as well:
- Pick from list: Choose an item from a comma-separated list.
- Lookup Table: Given a key and table - find the matching value.
- Line-item to Text: Converts a line-item field to text and separate values (sometimes referred to as "flatten"). Example here.
- Text to Line-item: Converts text to a line-item.
- Line Itemizer: Converts multiple text or line-item fields to line-items. Can be used to append or prepend to line-items. Examples start here.
- Import CSV: Imports CSV files as Line Items. Example here.
Make Your Own Formatters#
How to use Formatter Functions
Using Pick from list#
The Pick from list function can be accessed by choosing the Formatter app > Click "Utilities" > Choose "Pick from list" under Transform.
The Pick from list Text function lets you choose from a given list depending on the option you choose. The operations you can choose from are:
- Choose First
- Choose Last
So let's say you have a list of animals:
If you select the operation "Choose First," the Formatter would give you the output "Dog.
If you select the operation "Choose Last," the Formatter would give you the output "Turtle.
If you select the operation "Random," this would give you a random selection from the list. In this example, it gave us "Turtle", "Cat" and then "Fish"
You can also select the "Use a Custom Value" option to use an option other than first, last, or random, and enter a number in the "Custom Value for Operation" field. Keep in mind that the counting will begin at 0, though, so a custom value of 0 will return the first item in the list, a custom value of 1 will return the second item in the list, and so on. For example, if I select "Use a Custom Value" and use a "Custom Value for Operation" input of 2, the step will always return "Mouse", the third item in the list:
Using the Lookup Table#
The "Lookup Table" function is handy if you need to pass on one of a few various fields. For example if your Trigger app only passes on a name but your Action app needs an ID. You can create a list of records your Trigger can search for and then pass on the correct value.
To get started create a Formatter action and choose Utilities > and then "Lookup table" under Transform.
In the "Lookup Key" field you will want to pass on the trigger field you want to search your table for like this:
Using the "Lookup Table" section you will want to add each of the possible items you want to search for. The box on the left is the value you will search by and the box on the right is what will be returned with your Action. Keys are case-sensitive.
You can add more fields here by using the + icon like this:
Finally, you can optionally set a "Fallback Value". This will be returned if your search yields no results. This is an optional field.
When your Zap runs it will pull in your search result. In the example below, my Trigger app sent on "Micah" so my search result pulled in "ZAP23456Z":
You can then pass that on to your Action app's template like this:
Have a very large list, one that needs to be edited regularly, a list of exported data, or referenced by multiple steps/zaps? Consider instead using a Google Sheet to create a 2 column list, one with the source value and one with the destination value and reference it using the "Lookup Spreadsheet Row" search in the same way outlined above.
Using Default Value#
One super handy way to use the Default Value option is to create a fallback for fields that may not always be provided in your Trigger step.
Let’s say you have a web form that asks for user information and the Company field is optional, but your Action step requires that you add a company in order to create a lead.
Here’s how you could use Default Value to ensure that you use the company name when it’s available, but otherwise use “Not Available”.
Now on the step that has Company as required, you use the output of that Default Value step. If a value was present in the field you checked (Company), that will be used. If the field was empty, then the output will be what you used in the Default Value step.
Using Split Text#
Using Split Text to Separate Text Input Into Parts#
This is especially useful when a Trigger app provides a name in a Full Name field, but your Action app needs a First Name and Last Name.
The Split function can be accessed by choosing the Formatter app, clicking “Text”, and choosing “Split” from the Transform dropdown.
This function lets you input a line or block of text and break it apart at certain a character or phrase. You can then choose which segment of the text you’d like to output.
If you need to isolate just the first name from a “Full Name”-type piece of data, for example, you can map the “Full Name” data to the “Input” field.
You would then type the text you’d like to split on—in this case, a space—in the “Separator” field.
Note: Certain special characters need to be specified using special syntax. You can read more about that here: https://zapier.com/help/formatter/#special-characters
Finally, you would complete setup of this step by choosing which segment of the split text you'd like. In this case, where you want just the first name, you’d choose “First”.
Once this is set up, if your Zap triggers and the input field contains a full name like “Jane Smith”, the Formatter will return just “Jane”.
Then, to insert that first name in another app you would do the following:
If the app to which you're sending data requires a last name, you can repeat the steps above except instead of choosing "First" in the Segment Index you'd choose "Last".
→ Learn more from the Zapier Blog: How to Split Names and Other Text with Formatter.
Using Split Text to Extract Data#
There are times when you may need to extract a small portion of data from a larger set of info. For example, you receive notification emails when someone buys a product from you, and within the email you need to get the SKU number.
For the Trigger, use whichever app contains the data. In this case it’s Gmail. Notice that the SKU number is buried amongst other data.
The first step is to use Split Text to extract everything after the word SKU. In the Input field, add the source of your data. In our example it’s the Body Plain from Gmail. As the separator we’ll use the word “SKU” followed by a colon and a space (this means our output won’t have a space before it). We’ll keep the second segment.
Now we’ve got the SKU number but also that additional paragraph.
Let’s isolate the SKU number by using the output of our first Split Text step, and keeping the first segment. As a separator we’ll use [:newline:] which will take everything before that blank line between the SKU and the paragraph.
Now we’ve got the SKU number as its own piece of data, to use in other steps.
The final — and probably most important — step is to use that extracted data in our next Action step. You must make sure to use the output of our last Split Text step and not the original data from the Trigger.
→ Learn more from the Zapier Blog: How to Extract Phone Numbers, Email Addresses, and Links with Formatter.
There are many ways to use the Replace option, but here’s an example of how to change a comma-separated list of items and put them on their own individual line instead.
Let’s say you wanted to get the tags from an Instagram photo and place them on their own line of an email you want to send.
Looking at the test step of the email step, here’s how it would appear by default.
Let’s fix that. Here we specify the Input as the tags from Instagram. For the Find field we use the comma. The Replace field is optional, and if you leave it empty you will replace the comma with nothing, in essence just removing it. But in this case we use [:newline:] to drop each tag onto its own line.
Using the output of our Replace step, we now see that the commas have been removed and each tag occupies a line of its own.
→ Learn more from the Zapier blog: How to Find and Replace Text with Formatter.
Converting a datetime value to a UNIX/Epoch timestamp#
The UNIX timestamp is a format for representing a date/time as the number of seconds that have elapsed since January 1, 1970. For example,
1493422281 is the UNIX time for
28 April, 2017 23:31:21 GMT.
You can use a Formatter take a datetime in different format (like
2017-04-06T12:15Z) and convert it to a UNIX timestamp.
Let’s say you need to enter a UNIX timestamp in a Hubspot field (we'll call it "Contact Birthday"):
- Add a step to your Zap before the Hubspot action
- Choose Formatter by Zapier > Date/Time > Format
- Set it up like this—insert the datetime field you need to convert in the Input field:
- Then, in the Hubspot step (or whatever action step you're using), insert the output from the Formatter step in the "Contact Birthday" field
If you need to convert a UNIX timestamp to another date format, just switch what you put for the "From Format" and "To Format" fields in the Formatter step!
→ Learn more from the Zapier blog: How to Format Dates and Times with Formatter.
Converting Markdown text to HTML or PDF#
Want to write in Markdown, then use the formatted text in other apps—perhaps in WordPress to publish a blog post, or with Google Cloud Print to save a formatted PDF or print out your document? Formatter can convert your text for you. Here's how:
- Add a step to your Zap before the action where you want to use your text
- Choose Formatter by Zapier > Text > Convert Markdown to HTML
- Add your Markdown formatted text in the Input field
- Then add another step to use your text, using the output from the Formatter step in the field where you want formatted text. If you want to make a PDF file, use Google Cloud Print as the following action, and choose to save your file to Google Drive to get a PDF instead of printing out the document.
→ Learn more from the Zapier blog: How to Convert Markdown Text with Formatter.
How to use Line-items with Formatter
Line-items are a special type of Zapier field that contain a list of multiple values (also known as an array). Many invoicing, accounting and form applications support line-item fields as a way to more easily represent their data. You can identify this type of field in Zapier when you look at the results of a Test Step and see a list of numbered rows. Here is a simple example with one value per line:
and a more complex example with many values per line:
Zapier will take this output and create line-item fields for use in the next Zapier Step, but what do you do when you need to transform each of the values in these line-item fields? That is where Formatter comes in, it is now "line-item" aware. Any transform that works on one field value will now work on all values in a line-item field. Here are some examples of how to use Formatter with these line-item fields:
Transform each Date in a Line-item Field#
Date formatting is very application specific, especially as you move between different geographies. Here is how to transform a line-item date field from a US date format to an EU format using the Formatter Date/Time Format Transform:
Trigger application provides line-item US dates:#
Formatter Step with date transform for EU date format:#
Results of Formatter Step with dates in EU format:#
Calculate Line Price for each Product Purchased#
Some online sales applications provide the quantity of each product purchased, the price of each product purchased, but no line price for each product. Here is how to accomplish that with Formatter, two line-item fields and the Spreadsheet-Style Formula Transform.
Trigger application provides line-item price:#
Trigger application provides line-item quantity:#
Formatter Spreadsheet-Style Formula Transform calculation that multiplies line-item values from each field:#
Results of Formatter Step (Quantity * Price):#
Many invoicing applications are currency agnostic, so if you need to take those generic invoice amounts and format them for a specific currency, here is how you would do that with Formatter, a line-item field and the Format Currency Transform:
Transform SKUs from an Online Sales Application to an Invoicing Application#
Many invoicing applications require the use of their specific product SKUs. Many on-line sales apps generate their own unique product SKUs. How do you then, match the product purchased with the product invoiced? Formatter's Lookup Table Utility and line-item fields, that's how:
Create your own Line-items for an invoicing Action using the Line Itemizer Utility#
Not all Trigger apps are capable of providing data in line-item format (in order to add lines to an invoice Action, for example). Luckily, we have a way to do it using Formatter's Line Itemizer Utility...
Let's say you’re using QuickBooks Online and you need to create multiple line-items in an invoice with the following information:
You can use one Formatter step to convert that data into the Line-item format that the Invoice Step needs.
Here's how that step would look:#
Here are the line-item values we can now map into the later Steps of our Zap:#
You then use the output of the Line Itemizer in the Invoice Step:#
Let’s see what that looks like with Setup Preview turned off:#
Here’s the resulting invoice in Quickbooks Online, with the line-items you created:#
Add Shipping Information to your Invoice using the Line Itemizer Utility#
Some e-commerce applications provide shipping as a separate item, with its own set of fields. Many invoice applications want shipping as a standard line in the line-item fields your Zap provides. The Formatter Line Itemizer Utility can facilitate this by appending (or prepending) the shipping value(s) to these standard line-item fields.
Here’s an example set of values you might have from your Trigger:#
You can append the shipping value along with static text values (if needed) in the Formatter Step:#
Here’s what that looks like with Setup Preview turned off:#
You then use the output of the Line Itemizer in the Invoice Step:#
Here’s the resulting invoice in Quickbooks Online, with the shipping information as a line-item:#
If you’d rather prepend the values so that the shipping line shows up first, then put the shipping values into the fields first, add a comma, and then map in the line items that you’re prepending to, like this:
Create Line Items with Subtotals using the Line Itemizer Utility#
Some apps, like Quickbooks Online, require an
Amount line item property to be supplied in addition to the
Quantity fields that you're getting from your Trigger app. The
Amount field should be equal to
Price multiplied by
If your Trigger app does not supply this value and your quantities are higher than 1, you can perform this calculation for multiple line items using either the Formatter Numbers Action (Spreadsheet Style Formula) or the Formatter Utility Action (Line Itemizer). Let's take a look at Line Itemizer.
First, map in your existing Line item values and change the
Create Subtotal Property? field from
Here's an example of how the Line Items would be mapped out:#
Here's the first two line items from testing that step where we can see the Subtotal has been calculated:#
In your Quickbooks Online or other action requiring a
Amount value, you can map in the resulting Subtotal property along with the
Quantity properties in their appropriate fields.
The Subtotal calculator also works great when you're appending values such as a shipping line item to your line items:#
Create a Tag Text Field using the Line-item to Text Utility#
There are some instances when you need your Line-item field as formatted text so it can be used in an Action that doesn't support line-items. This is sometimes referred to as "flattening" a line-item. Tags are an instance of this, as it seems each application uses their own format to delineate multiple tags, and most don't support line-items. Hubspot is an example here, it uses a string of text, with each tag delineated by a
; Here is how you'd get this Tag format for Hubspot using the Line-item to Text Utility:
The Trigger application provides Tags as a line-item field, in this case a product category tag:#
Let's run the category field this through the Line-item to Text utility, replacing the
Now we are ready for the Hubspot Create/Update Contact Step to add this to the tags field:#
The tags are now part of newly created Hubspot contact#
Convert a Line Item Property to Multiple Single Values using the Line Item to Text Utility#
If you need to use a specific value that is part of a series in a line item in your Actions, you can separate those values. One way to do that is to map the line item into the input of the Line Item to Text Utility:
Note that you don't need to enter a separator. This utility is also used for 'flattening' line items into a single piece of text, and you can add the separator between each value. The separator won't be added to the individual values. Let's take a look at the output:
You can see that each part of the Line Item was returned as
item_n. The first value in the Line Item will always be
item_1 and the last item is repeated as
item_last in case you always need to extract the last item from the Line Item property being input into this transform.
How to Import CSV Files with Formatter
The Formatter Import CSV File Utility can be used as an Action Step in your Zap to import CSV files. You'll find it in the Utilities section:
It uses a File type field for input, so you can import a File field from a previous step, a public URL that points to your CSV file, or even text entered in CSV format. The existence of a header row is determined based on the content of the CSV file. In some cases, this check doesn't work, so you can use the "Force First Row as Header Row" option by selecting "yes" from the menu.
The type of CSV file you are importing is also automatically determined. If in your Test Step you find that this did not work, you can use the Type of CSV File option to choose the specific file type (including one column):
In the example below, we are using a File field from a Google Drive Trigger Step that has a header row:
Its output is four fields:
dialect - was the dialect automatically determined (default), or chosen from the Type of File option?
header - was a header found in the CSV file or forced by the option.
csv_text - a text field that includes all of the text from the CSV file.
line-items - line-item fields for each of the CSV file's column values
If you wanted to import your CSV into a Google Sheet, you could add the "Create Spreadsheet Row(s)" Action, and then map the line-item fields from your Formatter Import CSV Step:
CSV (Comma Separated Values) files are a special kind of formatted text file that are used to store column-oriented data, much like a spreadsheet. In their simplest form, there is a header row, followed by multiple rows of data, with each column separated by a comma. A simple example is here:
Exceptions are the rule with CSV files, though, and many CSV files are labeled as such but don't have this kind of formatting. The Formatter Import CSV File transform will try to interpret your format and do the right thing, but there are many edge cases when it won't be able to. You can use the Type of CSV File option to try to help with this interpretation, but not all CSV files can be imported.
Common Problems with the Import CSV File Utility#
I am not seeing my full CSV file when I do a Test Step
As CSV files can be rather large, we limit the Test Step to 1000 characters of text and 50 line-items. Please let us know if this is not enough for you to set up your Zap.
CSV Import only supports file sizes < 150K error
The utility only supports importing files that are 150K or less (which is around 1000 rows of a 10 column CSV file.) You'll need to split the CSV file into multiple files if it's too large.
Could not determine delimiter error
The CSV file you are trying to import doesn't use a recognized delimiter (commas, semi-colons, tabs), or the delimiter was not able to be recognized. If you know your delimiter or you have a One Colum CSV, you can choose that in the Type of CSV File option and hopefully that will help the transform determine this.
"utf8 codec can't decode byte xxx in position yyyy: invalid continuation byte"
Import CSV File only supports ASCII and UTF-8 encoding today. You'll need to re-encode your CSV in order to import it into your Zap.
My Action App does not support Line-Items
Many Zapier integrations don't support line-items fields. One option here is to use Google Sheets and its "Create Spreadsheet Row(s)" Action, which accepts line-item fields mapped to columns. Once the data is in the sheet, then other Zaps can be created to manipulate it.
Common Problems with Formatter
My Formatter step is not changing my data#
After you’ve set up and tested your Formatter step, remember to map the output of the step one of your action steps instead of the original data, which does not change.
For example, imagine a Zap where your trigger step collects a user’s full name through a question in SurveyMonkey, and you’ve set up a Formatter step to split out just the first name for use in an email. If you have the Zap set up like this, the full name will be used each time:
If you map the output of your Formatter step here, instead, though, just the first name will be used:
The date format I want is not in the list of choices#
If you the format you need is not available, you can manually enter one by clicking on the dropdown and select "Use Custom Value". See the Date & Time section for more info on the syntax of the format.
How does the "Second to Last" segment index of the Split text function work?#
The "Second to Last" segment would be the segment BEFORE the last index.
If you have a string of text to split that has 4 words: "Rip Van Old Winkle"
- First segment: "Rip"
- Second segment "Van"
- Second to last: "Old"
- Last Segment: "Winkle"
If you have a string of text to split that has 3 words: "Ned Eddard Stark"
- First segment: "Ned"
- Second segment "Eddard"
- Second to last: "Eddard"
- Last Segment: "Stark"
If you have a string of text to split that has 2 words: "John Snow"
- First segment: "John"
- Second to last: "John"
- Last Segment: "Snow"
I want to split a name field from the trigger into a first and last name field in the action#
You can do this via the Formatter Text action using the Split transform and using a space as your character. You'll need two actions - one to grab the first segment and one to grab the second. However, there are some things to be aware of. If someone's name is Vicky Cassidy, then this works great - you'll split Vicky and Cassidy.
If, however, someone's name is Vicky Van Cassidy, then you'll get Vicky as the first name and Van as the last name. If someone's name is Vicky Sue Cassidy then you'll get Vicky as the first name and Sue as the last name.
For an added level of security you can do three segments with three Formatter steps like this:
Using a custom value of 2 will get your the third segment - it's counterintuitive, but the first segment = 0. Then in the action you'll map the output of 1 to First Name and the output of 2 and 3 to Last name.
However, this still won't be perfect for people with +3 names or people with 2 words as a first name, so there is still a risk. We recommend not using a split but instead mapping the full name to either the first or last name field in the action.
Sometimes my data is blank, empty, unchanged, or not in the right format#
Formatter's handling of blank and invalid values varies by the type of Action being used. Here is a summary of the behavior you can expect:
- Blank values return blank (
- For transforms that involve parsing/extracting text, when it fails, returns blank
- Formatting (Number, Currency, Phone Number)
- Blank values return blank (
- When formatting fails to produce a sensible value, will return the input unaltered (so "N/A" stays "N/A")
- Blank values return blank (
- Operations (i.e. add)
- Skip non-numerical inputs and return the result of performing the operation on numerical inputs
- If all inputs are blank, will return
- Spreadsheet Formula that fail to run correctly result in an error
- Blank values return blank (
- When a valid date cannot be parsed from the input, returns "now", the moment in time the Zap is executing
The "Extract Pattern" transform isn't giving me text output.#
The best option here is to include a capture group with your regex. The Extract Pattern Transform will only return the text if there is a capture group included - for example, if you are searching for the text "three" within "one two three four", you should put "three" in parens as shown here:
That will output:
If you are not able to set that up, the output of Formatter's Extract Pattern Transform does not provide the extracted text, but instead provides these three fields:
- _end - last position of match in text
- _start - first position of match in the text
- _matched - true or false based on the match
Several Formatter steps can be used to extract the text using these Extract Pattern position outputs - specifically, a Math Operation step and a Truncate step. Please contact support for assistance with setting this up.
My Formatter Step Is Incorrectly Converting the Date#
Usually when the Formatter step's "From Format" field is not set (screenshot below)
This ends up throwing off the Formatter app and creates an incorrect conversion. For example, if you refer to the gif below, it shows that the date provided to the Formatter step is 11/05/2018 but the output is 01/01/18
But once you set the "From Format", this helps the Formatter figure out the format of the date and create an accurate conversion.
In the gif below, it shows that the date 11/05/2018 (DD/MM/YYYY) was converted to 05/11/2018 (MM/DD/YYYY) correctly:
My Format Phone Number Step Is not modifying the phone number#
When formatting a phone number using the Formatter > Number > Format Phone Number transform, Zapier validates whether or not the "from" phone number is valid in the country code selected (US is the default).
Fake phone numbers (e.g., there is no 555 area code in the US) or a "phone number" with too many or not enough numbers, or phone numbers with invalid characters will result in an unchanged output.
Connect the apps you use every day
Get started with a Free account