Formula Functions Reference
Formula Functions Reference
This is your complete guide to every formula function available in TexAu. Each function is explained with real examples
you can copy and adapt for your own data.
Logic Functions
IF - Conditional Logic
What it does: Returns one value if a condition is true, another if false. The foundation of smart formulas.
Syntax:
IF(condition, value_if_true, value_if_false)
Examples:
1. Categorize companies by size:
IF(employee_count > 500, "Enterprise", "Mid-Market")
Result: Rows with >500 employees get "Enterprise", others get "Mid-Market"
2. Flag high-value deals:
IF(deal_value > 50000, "High Priority", "Standard")
Result: Deals over $50k are flagged for your sales team
3. Nested IF (if this, then that, else if this other thing...):
IF(revenue > 1000000, "Tier 1", IF(revenue > 100000, "Tier 2", "Tier 3"))
Result: Three-tier classification based on revenue brackets
Tips:
- You can nest IF functions up to 5 levels deep
- Conditions use operators like >, <, ==, !=, etc.
- Think of it as: "If this is true, do this. Otherwise, do that."
AND - All Conditions Must Be True
What it does: Returns true only if ALL conditions are met. Useful for complex filtering logic.
Syntax:
AND(condition1, condition2, condition3, ...)
Examples:
1. Find qualified leads:
AND(industry == "Technology", revenue > 100000, hiring_this_quarter == true)
Result: Returns true only for tech companies with 100k+ revenue that are actively hiring
2. Check data completeness:
AND(email != "", phone != "", company != "")
Result: True only if all three fields have values
3. Combine with IF for advanced filtering:
IF(AND(status == "active", plan == "enterprise"), "Renewal Candidate", "Other")
Result: Identify companies eligible for enterprise renewals
Tips:
- AND is strict-all conditions must pass
- Use with IF to take action when complex conditions are met
- Common in data quality checks
OR - At Least One Condition Must Be True
What it does: Returns true if ANY condition is met. Less strict than AND.
Syntax:
OR(condition1, condition2, condition3, ...)
Examples:
1. Identify decision makers:
OR(title == "CEO", title == "CTO", title == "CFO")
Result: True for anyone in executive roles
2. Multi-industry targeting:
OR(industry == "SaaS", industry == "FinTech", industry == "Healthcare")
Result: True for your target industries
3. Find incomplete records:
IF(OR(email == "", phone == ""), "Incomplete", "Complete")
Result: Flag records missing critical contact info
Tips:
- OR is more permissive than AND
- Great for categorizing into "buckets" (e.g., "is this a target industry?")
- Combine with IF for conditional logic
NOT - Negates a Boolean
What it does: Flips true to false and false to true. Simple and effective.
Syntax:
NOT(condition)
Examples:
1. Find non-customers:
NOT(is_customer == true)
Result: Everyone who isn't already a customer
2. Identify unverified leads:
IF(NOT(email_verified), "Needs Verification", "Verified")
Result: Flag leads that haven't been verified yet
3. Complex negation:
NOT(AND(industry == "Competitor", deal_stage == "Closed"))
Result: Everyone except competitors who've closed deals with you
Tips:
- NOT is simpler than writing condition == false
- Often used with AND/OR for advanced logic
INCLUDES / CONTAINS - Check if Text Contains a Substring
What it does: Returns true if a text field contains the search word or phrase. Case-insensitive (doesn't care about
uppercase/lowercase).
Syntax:
INCLUDES(text, search)
CONTAINS(text, search) # Same thing, different name
Examples:
1. Find specific companies:
INCLUDES(company_name, "Inc.")
Result: True for "Acme Inc.", "Tech Inc.", etc.
2. Identify personal emails:
INCLUDES(email, "@gmail.com")
Result: True for anyone with a Gmail address
3. Check for red flags in company description:
IF(INCLUDES(company_description, "bankruptcy"), "High Risk", "Normal")
Result: Flag companies mentioned in bankruptcy proceedings
4. Multi-word search:
OR(INCLUDES(title, "Manager"), INCLUDES(title, "Director"))
Result: Find anyone in management-level roles
Tips:
- INCLUDES and CONTAINS work identically (use whichever feels natural)
- Searches are case-insensitive ("Gmail" matches "gmail")
- Searches partial matches, so "Inc" finds "Inc.", "Incorporated", etc.
- Use with OR to search for multiple keywords
Text Functions
CONCAT - Join Text Strings Together
What it does: Combines multiple text values into one. The workhorse of text formulas.
Syntax:
CONCAT(value1, value2, value3, ...)
Examples:
1. Create a full name:
CONCAT(first_name, " ", last_name)
Result: "John Smith" (from "John" + " " + "Smith")
2. Build a link title:
CONCAT(first_name, " at ", company_name)
Result: "John at Acme Corp" (great for display lists)
3. Create an email subject:
CONCAT("Outreach to: ", company_name, " (", city, ")")
Result: "Outreach to: Acme Corp (San Francisco)"
4. Combine address fields:
CONCAT(street, ", ", city, ", ", state, " ", zip)
Result: "123 Main St, Denver, CO 80202"
Tips:
- Always include separators (" ", ", ", "-") explicitly-CONCAT doesn't add them automatically
- You can CONCAT as many values as you need
- Mix column references with quoted text freely
LEN / LENGTH - Count Characters
What it does: Counts how many characters are in a text field. Useful for quality checks.
Syntax:
LEN(text)
LENGTH(text) # Same thing, different name
Examples:
1. Check if description is substantial:
IF(LEN(company_description) > 100, "Detailed", "Brief")
Result: Categorize by description length
2. Flag unusually short emails:
IF(LEN(email) < 5, "Likely Invalid", "Probably Valid")
Result: Catch obviously wrong email addresses
3. Verify phone number length:
IF(LEN(phone_number) >= 10, "Valid Format", "Needs Review")
Result: Simple phone validation (assuming 10+ digits is valid)
Tips:
- LEN counts every character, including spaces
- Useful for data quality checks before sending to customers
- Combine with IF to flag problematic records
UPPER - Convert to Uppercase
What it does: Changes all letters to uppercase. Useful for standardizing data.
Syntax:
UPPER(text)
Examples:
1. Standardize state abbreviations:
UPPER(state)
Result: "ca" becomes "CA", "New York" becomes "NEW YORK"
2. Create email addresses with consistent formatting:
CONCAT(UPPER(first_name), ".", last_name, "@company.com")
Result: "[email protected]"
3. Clean up messy industry data:
UPPER(industry)
Result: All industries in consistent uppercase
Tips:
- UPPER affects letters only-numbers and symbols stay the same
- Great for standardizing before comparing values
LOWER - Convert to Lowercase
What it does: Changes all letters to lowercase. Handy for standardization.
Syntax:
LOWER(text)
Examples:
1. Standardize domains:
LOWER(domain)
Result: "ACME.COM" becomes "acme.com"
2. Create standardized email for matching:
LOWER(email)
Result: Emails in consistent lowercase for deduplication
3. Normalize text before comparison:
LOWER(status) == "active"
Result: Catches "ACTIVE", "Active", "active" all as equivalent
Tips:
- LOWER is case-insensitive; it works on any text
- Often used before comparisons to handle inconsistent data entry
- Combine with TRIM for thorough data cleanup
TRIM - Remove Leading and Trailing Spaces
What it does: Strips away extra spaces at the beginning and end of text. Great for cleaning up messy data.
Syntax:
TRIM(text)
Examples:
1. Clean up company names:
TRIM(company_name)
Result: " Acme Inc. " becomes "Acme Inc."
2. Standardize before comparison:
TRIM(title) == "Sales Manager"
Result: " Sales Manager " now matches "Sales Manager"
3. Combine with other functions:
UPPER(TRIM(industry))
Result: Removes spaces AND converts to uppercase in one shot
Tips:
- TRIM removes spaces from both ends, but not from the middle
- Very useful for data imported from messy sources
- Combine with UPPER/LOWER for comprehensive cleanup
- Note: TRIM removes spaces only, not other whitespace characters
REPLACE - Replace Text
What it does: Finds text and replaces it with something else.
Syntax:
REPLACE(text, old, new)
Examples:
1. Remove unwanted characters:
REPLACE(phone_number, "-", "")
Result: "555-123-4567" becomes "5551234567"
2. Standardize domain names:
REPLACE(domain, "www.", "")
Result: "www.acme.com" becomes "acme.com"
3. Fix common typos:
REPLACE(company_name, "Corp", "Corporation")
Result: "Acme Corp" becomes "Acme Corporation"
4. Replace multiple patterns (stack REPLACE):
REPLACE(REPLACE(phone, "-", ""), " ", "")
Result: Removes both dashes and spaces
Tips:
- REPLACE is case-sensitive ("Corp" won't match "corp")
- Use REPLACE multiple times to handle multiple replacements
- Great for normalizing formats
SPLIT - Split Text Into Parts
What it does: Breaks a text string into pieces using a separator. Useful for extracting parts of structured data.
Syntax:
SPLIT(text, delimiter)
Examples:
1. Extract domain from email:
SPLIT(email, "@")[1]
Result: "[email protected]" → "acme.com" (takes the second part after @)
2. Get the first word:
SPLIT(title, " ")[0]
Result: "Vice President Sales" → "Vice" (takes the first part before the space)
3. Parse a code:
SPLIT(product_code, "-")[0]
Result: "PROD-2024-001" → "PROD"
Tips:
- SPLIT returns an array; use [0] for first part, [1] for second, etc.
- Counting starts at 0: first piece is [0], second is [1]
- Super useful for data that follows patterns
SUBSTRING - Extract Part of Text
What it does: Grabs a portion of text based on starting position and length.
Syntax:
SUBSTRING(text, start_position, length)
Examples:
1. Extract year from date string:
SUBSTRING(date_string, 0, 4)
Result: "2024-01-15" → "2024"
2. Get first 3 characters:
SUBSTRING(company_name, 0, 3)
Result: "Acme Corporation" → "Acm"
3. Extract specific portion:
SUBSTRING(phone, 0, 3)
Result: "5551234567" → "555" (area code)
Tips:
- Counting starts at position 0
- Length is how many characters to extract
- Useful for fixed-format data
AFTER - Get Text After a Delimiter
What it does: Returns everything after a specified separator. Simpler than SPLIT for single extractions.
Syntax:
AFTER(text, delimiter)
Examples:
1. Extract domain from email:
AFTER(email, "@")
Result: "[email protected]" → "acme.com"
2. Get text after a slash:
AFTER(url, "/")
Result: "https://acme.com/about" → "acme.com/about"
3. Extract initials after title prefix:
AFTER(employee_code, "-")
Result: "DEPT-ABC123" → "ABC123"
Tips:
- AFTER grabs everything AFTER the delimiter (delimiter isn't included)
- Simpler than SPLIT if you only need what comes after one separator
- If the delimiter doesn't exist, you get the original text back
Data Functions
COALESCE - Return First Non-Empty Value
What it does: Goes through a list of values and returns the FIRST one that isn't empty. Critical for waterfall logic and
data quality.
Syntax:
COALESCE(value1, value2, value3, ...)
Examples:
1. Use multiple email sources (waterfall pattern):
COALESCE(personal_email, work_email, backup_email)
Result: Returns personal_email if it has a value. If empty, tries work_email. If that's empty, tries backup_email. If
all are empty, returns empty.
2. Fill in missing phone numbers:
COALESCE(mobile_phone, office_phone, home_phone)
Result: Prioritizes mobile, falls back to office, then home
3. Company name from multiple sources:
COALESCE(official_company_name, registered_name, display_name)
Result: Uses the most official name available
4. Fallback to a default:
COALESCE(city, "Not Provided")
Result: Shows "Not Provided" if city is missing
Tips:
- COALESCE is your best friend for waterfall workflows
- Order matters-it checks in the order you list them
- Perfect for handling missing data gracefully
- Can combine with IF for conditional logic: IF(COALESCE(phone, "") == "", "No Phone", "Has Phone")
Math Functions
ABS - Absolute Value
What it does: Returns the positive version of a number (removes the negative sign if there is one).
Syntax:
ABS(number)
Examples:
1. Standardize variance:
ABS(revenue_variance)
Result: -5000 becomes 5000 (shows magnitude regardless of direction)
2. Calculate deviation:
ABS(target_revenue - actual_revenue)
Result: Shows how far off you are, whether over or under
3. Find absolute price difference:
ABS(list_price - actual_price)
Result: Shows discount amount regardless of which is higher
Tips:
- ABS is useful when you care about magnitude, not direction
- Common in analytics and performance tracking
MIN - Smallest Value
What it does: Returns the smallest number from a list.
Syntax:
MIN(number1, number2, number3, ...)
Examples:
1. Find lowest quote among providers:
MIN(vendor_a_price, vendor_b_price, vendor_c_price)
Result: Returns the cheapest option
2. Find fastest delivery time:
MIN(standard_days, express_days, overnight_days)
Result: Shows the quickest option available
3. Floor a value (min of value or 0):
MIN(profit, 0)
Result: Returns profit, but never below 0 (loss becomes 0)
Tips:
- MIN compares numbers only; convert text to numbers if needed
- Useful for finding the best (lowest cost, fastest time) option
MAX - Largest Value
What it does: Returns the largest number from a list.
Syntax:
MAX(number1, number2, number3, ...)
Examples:
1. Find highest revenue quarter:
MAX(q1_revenue, q2_revenue, q3_revenue, q4_revenue)
Result: Shows the best-performing quarter
2. Find maximum lead score:
MAX(engagement_score, fit_score, activity_score)
Result: Shows the highest score across categories
3. Cap a value:
MIN(quota_attainment, 1.0)
Result: Never exceeds 100% (though this uses MIN, not MAX)
Tips:
- MAX returns the highest value
- Pair with MIN for range analysis
- Useful for finding peaks in data
ROUND - Round to Decimal Places
What it does: Rounds a number to a specific number of decimal places.
Syntax:
ROUND(number, decimals)
Examples:
1. Round to nearest dollar:
ROUND(monthly_cost, 0)
Result: 123.456 becomes 123
2. Round to 2 decimal places:
ROUND(percentage, 2)
Result: 75.6789 becomes 75.68
3. Monthly cost from annual:
ROUND(annual_cost / 12, 2)
Result: Divides and rounds to nearest cent
Tips:
- Use 0 decimals for whole numbers
- Use 2 decimals for currency
- Rounding follows standard rules (0.5 rounds up)
FLOOR - Round Down
What it does: Rounds down to the nearest whole number (or specified decimal). Always goes down.
Syntax:
FLOOR(number)
Examples:
1. Calculate complete years:
FLOOR(days_as_customer / 365)
Result: 1463 days becomes 4 years (not 4.01)
2. Full months:
FLOOR(tenure_in_days / 30)
Result: 127 days becomes 4 months
3. Downgrade partial quantities:
FLOOR(inventory / 12)
Result: 47 items become 3 dozen (3.91 becomes 3)
Tips:
- FLOOR always rounds down, even if .9
- Useful for "complete units only" scenarios
- Opposite of CEIL
CEIL - Round Up
What it does: Rounds up to the nearest whole number (or specified decimal). Always goes up.
Syntax:
CEIL(number)
Examples:
1. Calculate billable hours:
CEIL(task_minutes / 60)
Result: 65 minutes becomes 2 billable hours (not 1.08)
2. Number of pages needed:
CEIL(total_words / 500)
Result: 2500 words becomes 5 pages (not 4.87)
3. Calculate seats needed:
CEIL(employees / 5)
Result: 23 employees need 5 groups of 5
Tips:
- CEIL always rounds up, even if .1
- Useful for "we need at least" scenarios
- Opposite of FLOOR
Quick Function Finder
Need to combine text? → CONCAT Need to check if something is inside something? → INCLUDES Need to pick from multiple
options? → IF, OR Need to get a first non-empty value? → COALESCE Need to clean up spaces? → TRIM Need to extract part
of a text? → SUBSTRING, AFTER, SPLIT Need to do math? → Basic operators (+, -, *, /), MIN, MAX, ROUND, ABS Need to
handle missing data? → COALESCE, IF
What's Next?
- Back to Formula Basics if you need a refresher
- Ready for processing? Check out Built-in Processing Actions
- Ready to schedule? Setting Up Scheduled Jobs
