Formatter by Zapier Help & Support

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:

• Text
• Numbers
• Date & Time
• Misc Utilities

Text#

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.

Reformatting Text#

• 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.

Editing Text#

• 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.

Special Characters#

Sometimes you want to find/replace or split on special characters. For these cases, you can use a special character syntax:

• [:space:] - matches space (\s) characters
• [:tab:] - matches tab (\t) characters
• [:newline:] - matches newline (\n) characters
• [:return:] - matches carriage-return (\r) characters

Numbers#

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#

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.

Formula Examples#

• (1 + 2) / 4 returns 0.75
• POW(2, 4) returns 16
• IF(ISEVEN(2), 100, 200) returns 100
• AND(ISODD(1), ISEVEN(2)) returns TRUE
• ROUNDUP(1234.567 * 106%, 2) returns 1308.65
• IF(1 < 2, IF(3 = 3, "you did it!", 20), 30) returns you did it!
• 100 * (VALUE("123" & "." & "01") + 7) returns 13001

Formula Functions#

ABS(number)

Returns the absolute value of a number

ACOS(number)

Returns the inverse cosine of a number

ACOSH(number)

Returns the inverse hyperbolic cosine of a number

AND(condition, [condition, ...])

Returns TRUE if all of the conditions evaluate to TRUE, otherwise FALSE

ASIN(number)

Returns the inverse sine of a number

ASINH(number)

Returns the inverse hyperbolic sine of a number

ATAN(number)

Returns the inverse tangent of a number

ATAN2(x, y)

Returns the inverse tangent of a pair of x and y coordinates

ATANH(number)

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.2 and CEILING(3.14159, 2) = 4)

COS(number)

Returns the cosine of a number

COSH(number)

Returns the hyperbolic cosine of a number

DEGREES(radians)

Returns radians converted to a value in degrees

EVEN(number)

Rounds a number up to the next even number

EXP(number, power)

Returns the constant e raised to a power

FACT(number)

Returns the factorial of a number

FACTDOUBLE(number)

Returns the double factorial of a number

FALSE()

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.1 and 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

INT(number)

Returns a number rounded down to the previous integer

ISBLANK(value)

Returns TRUE if a value is blank (empty); Otherwise, returns FALSE

ISEVEN(value)

Returns TRUE if a value is an even number; Otherwise, returns FALSE.

ISLOGICAL(value)

Returns TRUE if a value is a logical value; Otherwise, returns FALSE

ISNONTEXT(value)

Returns TRUE if a value is NOT text; Otherwise, returns FALSE

ISNUMBER(value)

Returns TRUE if a value is a number; Otherwise, returns FALSE

ISODD(value)

Returns TRUE if a value is an odd number; Otherwise, returns FALSE

ISTEXT(value)

Returns TRUE if a value is text; Otherwise, returns FALSE

LCM(number, number, [number, ...])

Returns the least common multiple of two or more numbers

LN(number)

Returns the natural logarithm of a number

LOG(number, base)

Returns the logarithm of a number, to a specified base

LOG10(number)

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

NOT(condition)

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)

ODD(number)

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

PI()

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

RADIANS(degrees)

Returns degrees converted to a value in radians

RAND()

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

SIGN(number)

Returns the sign (+, -, 0) of a number

SIN(number)

Returns the sine of a number

SINH(number)

Returns the hyperbolic sine of a number

SQRT(number)

Returns the positive square root of a number

SQRTPI(number)

Returns the square root of a number multiplied by the constant Pi

SUM(number, [number, ...])

Returns the sum of a list of numbers

TAN(number)

Returns the tangent of a number

TANH(number)

Returns the hyperbolic tangent of a number

TRUE()

Returns the logical value TRUE

TRUNC(number, [places])

Returns a number truncated to a certain number of significant digits by omitting less significant digits

VALUE(text)

Returns the text converted to a numeric value

Formula Arithmetic#

Negation Operator

-A converts A to a negative number

Percent Operator

A% converts A to a percentage value by dividing it by 100

Exponentiation Operator

A ^ B raises A number to the power of B

Multiplication Operator

A * B multiplies A by B

Division Operator

A / B divides A by B

Addition Operator

A + B adds B to A

Subtraction Operator

A - B subtracts B from A

Concatenation Operator

"A" & "B" concatenates the text value A and B into a single text value "AB"

Formula Comparisons#

Equal Condition

A = B is TRUE when A is equal to B

Not Equal Condition

A <> B is TRUE when A is not equal to B

Greater Than Condition

A > B is TRUE when A is greater than B

Less Than Condition

A < B is TRUE when A is less than B

Greater Than or Equal Condition

A >= B is TRUE when A is greater than or equal to B

Less Than or Equal Condition

A <= B is TRUE when A is less than or equal to B

Date Time#

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#

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:

• +1 hour
• -3 days
• +5 years +2 months -3 minutes
• +8h

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.

Misc Utilities#

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#

Need to reformat text, numbers, or dates into another format? Zapier's Code integration lets you tweak any fields from your apps with Javascript or Python code. Check out the Intro to Code Steps article for sample code to split names, reformat dates, and more.

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
• Random

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.

Using Replace#

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):#

Currency Formatting#

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 Price and Quantity fields that you're getting from your Trigger app. The Amount field should be equal to Price multiplied by Quantity.

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 no to yes.

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 Subtotal or Amount value, you can map in the resulting Subtotal property along with the Price and 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 , with ;#

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 backgrounder#

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.

For example:

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:

First action:

Second action:

Third action:

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:

Text Transforms:

• Blank values return blank ('')
• For transforms that involve parsing/extracting text, when it fails, returns blank

Number Transforms:

• 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")
• 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 0
• Spreadsheet Formula that fail to run correctly result in an error

Date/Time Transforms:

• 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.