Skip to main content

What are calculated columns?

Create a calculated column to use formulas to transform your extracted data.

M
Written by Melissa Shires
Updated yesterday

Calculated columns let you transform your extracted data by applying formulas to the data you've extracted. This allows you to:

  • Clean scraped data: fix formatting, remove symbols, standardize text, reformat text.

  • Calculate new values: percentages, scores, margins, custom metrics.

  • Combine or split fields: merge names, extract domains, restructure data.

  • Create smart fields: status flags, and categories, conditional logic.

Once created, these fields automatically run on every row of data during extraction, creating new columns with your processed results automatically.

How to create a calculated column

✅ To create a calculated column, you need an approved robot with at least one successful extraction. To learn how to create and approve a robot, check out our guide here.

  1. Go to your robot from the dashboard, and click Tables.

  2. Click the add calculated column and the calculated column editor will open.

  3. Name your calculated column.

  4. Build your formula.

  5. Click Add column to save.

The new column will appear in your table immediately and will apply to all existing and future data you extract.

[arcade id="sxeaeMqqhQWKHEWiKTv6" title="How to create calculated columns" padding-bottom="0"]

Syntax Basics

{column_name}

References column directly

"text"

Text in quotes

1

Numbers without quotes

function(params)

Function syntax

Tips for building your formula

💡 The calculated column editor has a library to help you create your calculated fields by providing you with parameters, examples and the output format.
​

  • Click any column name in the left panel to insert it as a reference.

  • Formulas must be entered in UPPER CASE to be recognized.

  • You can use already created calculated columns as a reference.

  • Type function names or search for them in the function list.

  • Basic math operators include addition (+), subtraction (-), division (/), and multiplication (*).

Examples

Clean currency values

  • Goal: convert "$1,299.99" to 1299.99

  • Column name: clean_price

VALUE(SUBSTITUTE(SUBSTITUTE({price},"$",""),",",""))

Combine text fields

  • Goal: merge first and last name

  • Column name: full_name

CONCATENATE({first_name}, " ", {last_name})

Simple calculation

  • Goal: calculate 20% discount

  • Column name: sale_price

ROUND({price} *0.8,2)

Conditional label

  • Goal: create stock status

  • Column name: stock_status

IF({quantity} > 0, "In Stock", "Out of Stock")

Troubleshooting

  • Formula not recognized

    • Make sure you're using UPPER CASE when entering your formula,

    • Do not start your formula with an "=" sign.

  • Unknown column name: check spelling and case of column names.

  • "#VALUE?": check the syntax of your formula.

Reference guide: Most used functions

Data cleaning

Convert text to number

VALUE({column})

Replace text

SUBSTITUTE({column}, old, new)

Remove extra spaces

TRIM({column})

Change case (upper or lower)

UPPER({column})
LOWER({column})

Remove all special characters from price

VALUE(REGEXREPLACE({price},"[^0-9.]",""))

Clean price

VALUE(SUBSTITUTE({price},"$",""))

Standardize phone numbers

REGEXP_REPLACE({phone},"[^0-9]","")

Calculations

Round to decimal places

ROUND({number}, digits)

Price per unit

{selling_price-cost}/{selling_price}*100

Text operations

Combine text

CONCATENATE({column1},{column2}, ...)

Get first character(s)

LEFT({column}, count)

Get first word

LEFT({column},SEARCH(" ",{text}))

Get last character(s)

RIGHT({column}, count)

Count characters

LEN({column})

Extract domain from URL

REGEXP_EXTRACT({url},"^(?:https?://)?([^/]+)")

Extract domain from email address

RIGHT({email},LEN({email})-SEARCH("@",{email}))

Extract email from text or HTML

REGEXEXTRACT({content}, "[a-zA-Z0-9][a-zA-Z0-9._%+-]*@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")

Extract first email when multiple exist

REGEXEXTRACT({content}, "([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]+)")

Extract email from HTML mailto links

SUBSTITUTE(REGEXEXTRACT({html}, "mailto:([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]+)"), "mailto:", "")

Title case

PROPER(LOWER({column}))

Date operations

Today's date

TODAY()

Days since

DAYS(TODAY(),{column})

Year

YEAR({column})

Quarter

=MONTH({column})/3

Check if expired 

IF(DAYS(TODAY(), {expiry_date}) > 0, "Expired", "Valid")

Date based categorization

IF(DAYS(TODAY(), {created_date}) > 30, "Old", "Recent")

Basic conditional logic

Check if column has data

IF({column}, "Has Value", "Empty")

Check if column matches logic conditions

IF({status} = "active", "Current", "Inactive")

Check if column equals

IF({country} = "USA", "Domestic", "International")

Check if column doesn't equal

 IF({status} != "completed", "Pending", "Done")

Check if column is greater than or equal to 

IF({rating} >= 4.5, "Highly Rated", "Standard")

Check if column is less than or equal to 

IF({days_old} <= 7, "New", "Older")

Status based on multiple fields

IF(AND({payment} = "paid", {shipped} = "yes", {delivered} = "yes"), "Complete", IF(AND({payment} = "paid", {shipped} = "yes"), "In Transit", "Processing"))

Categorization and segmentation

Categorize or segment based on column value

IF({price} > 100, "Expensive", "Affordable")

Categorize or segment based on multiple column values

IF(AND({price} < 100, {rating} > 4), "Best Deal", "Regular")
IF(OR({quantity} = 0, {status} = "discontinued"), "Unavailable", "Available")
IF(NOT({is_premium} = "true"), "Standard", "Premium")

Multiple categorization or segmentation

IF({score} >= 90, "Excellent", IF({score} >= 70, "Good", IF({score} >= 50, "Fair", "Poor")))

Pricing tiers

IF({price} < 50, "Budget", IF({price} < 200, "Mid-range", "Premium"))

Categorizing by greater than / less than

IF({quantity} > 10, "In Stock", IF({quantity} > 0, "Low Stock", "Out of Stock"))

Complex business logic

Discount eligibility

IF(AND({total} > 100, {member} = "yes"), {total} * 0.9, {total})

Lead scoring

IF(AND({company_size} > 100, {budget} > 50000), "Hot Lead", IF(OR({company_size} > 50, {budget} > 25000), "Warm Lead", "Cold Lead"))

Shipping cost estimates

IF({total} >= 100, 0, IF({weight} > 10, 25, 10))

Inventory alerts

IF({quantity}/{capacity} < 0.2, "Reorder Now", "Sufficient Stock")

Performance rating

IF({success_rate} >= 0.95, "Excellent", IF({success_rate} >= 0.8, "Good", "Needs Improvement"))

Comparing text

Check for specific text

IF({category} = "electronics", "Tech Product", "Other")

Check if contains (using SEARCH)

IF(ISERROR(SEARCH("premium", {plan})), "Standard", "Premium")

Multiple text options

IF(OR({status} = "new", {status} = "pending", {status} = "review"), "Needs Attention", "Processed")

Empty/blank values

Check if blank 

IF(ISBLANK({email}), "No Email", "Has Email")

Default value for empty fields 

IF(ISBLANK({discount}), 0, {discount})

Data validation

IF(OR(ISBLANK({name}), ISBLANK({email}), ISBLANK({phone})), "Incomplete", "Complete")

Error handling

Prevent division by zero

IF({quantity} = 0, "N/A", {total}/{quantity})

Handle missing data

IF(ISERROR({price} * {quantity}), 0, {price} * {quantity})
Related Articles
How are credits calculated?
How to integrate your robot with Make.com
Did this answer your question?