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