Clay Formulas: Transform and Clean Your Data

What Are Clay Formulas?

Formulas in Clay are spreadsheet-like expressions that transform, combine, and manipulate your data. They run automatically on every row in your table, making data cleaning and transformation effortless at scale.

If you've used Excel or Google Sheets formulas, Clay formulas will feel familiar. The main difference is that Clay formulas are designed specifically for sales and marketing data—cleaning company names, extracting domains from emails, formatting phone numbers, and building personalized outreach messages.

Basic Formula Syntax

Clay formulas reference columns using curly braces: {Column Name}. You can combine columns with text and functions:

Concatenation example:

"Hi " + {First Name} + ", I noticed " + {Company} + " is hiring..."

Referencing columns:

• {First Name} — References the "First Name" column
• {Company.Domain} — References nested data from enrichment
• {LinkedIn URL} — Exact column name match

Essential Text Functions

UPPER, LOWER, PROPER

Control text capitalization:

• UPPER({Company}) → "ACME INC"
• LOWER({Email}) → "john@acme.com"
• PROPER({Full Name}) → "John Smith"

TRIM and CLEAN

Remove unwanted whitespace and characters:

• TRIM({Company}) — Removes leading/trailing spaces
• CLEAN({Notes}) — Removes non-printable characters

LEFT, RIGHT, MID

Extract portions of text:

• LEFT({Phone}, 3) → First 3 characters (area code)
• RIGHT({Email}, 10) → Last 10 characters
• MID({LinkedIn URL}, 29, 20) → Characters 29-49

SPLIT and EXTRACT

Parse structured text:

• SPLIT({Email}, "@")[1] → Domain from email
• SPLIT({Full Name}, " ")[0] → First name

Conditional Logic with IF

The IF function lets you create different outputs based on conditions:

IF({Company Size} > 500, "Enterprise", "SMB")

Nested IF example:

IF({Revenue} > 10000000, "Enterprise", IF({Revenue} > 1000000, "Mid-Market", "SMB"))

Common Conditions

• {Field} == "value" — Equals
• {Field} != "value" — Not equals
• {Field} > 100 — Greater than
• CONTAINS({Field}, "text") — Text contains
• ISBLANK({Field}) — Field is empty

Handling Empty Values

Enrichment often returns empty values. Handle them gracefully:

IF(ISBLANK({Title}), "there", {First Name})

This outputs the first name if available, or "there" as a fallback for personalization.

Building Personalized Messages

Formulas are powerful for creating dynamic outreach content:

Personalized opener:

"Hi " + {First Name} + ", I saw " + {Company} + " is " + IF({Hiring}, "growing the team", "doing great work") + " in " + {Industry} + "."

Common Formula Recipes

Extract First Name from Full Name

SPLIT({Full Name}, " ")[0]

Clean Company Name

TRIM(REPLACE(REPLACE({Company}, "Inc.", ""), "LLC", ""))

Format Phone Number

"(" + LEFT({Phone}, 3) + ") " + MID({Phone}, 4, 3) + "-" + RIGHT({Phone}, 4)

Build LinkedIn Search URL

"https://linkedin.com/search/results/people/?keywords=" + URLENCODE({First Name} + " " + {Company})

Debugging Formulas

When formulas don't work as expected:

• Check column name spelling (case-sensitive)
• Look for missing quotes around text
• Test with a single row first
• Use ISBLANK() to handle empty values