Skip to content
Workflows

Format your data with workflows

Last updated: September 26, 2022

Applies to:

Operations Hub Professional, Enterprise

Use the Format data workflow action to fix, format, and maintain your CRM data. For example, you can capitalize contact or company names, or calculate values based on properties.

You can also convert data into single-line or multi-line text properties, or format and add data to a Google spreadsheet

Formatting data with workflows is a two-step process. First, you'll prepare your data by setting up a Format data action. Then, you'll set up another workflow action to use the data.

Prepare your data

To set up a data formatting action:

  • In your HubSpot account, navigate to Automation > Workflows.
  • Click the name of an existing workflow, or create a new workflow.
  • In the workflow editor, click the + plus icon to add a workflow action.
  • In the actions panel, select Format data. Data can be formatted with either default formatting options or with custom formulas (beta). In the right panel, ensure that the Custom mode switch is turned off.

Format your data with default formatting options

To format data with default formatting options:  
  • In the right panel, select the property or value to format:
    • To format property values of enrolled records, click to expand [Record] in this workflow, then select a property.
    • To format outcomes of previous actions, click to expand the Previous action name, then select an action output.
  • Then, select a formatting option:
    • Add a number: add a specific number to the property or value. Enter the desired value into the Number to add field.
    • Add an amount of time: add an amount of time (e.g. hours, days) to a datetime value. Enter the amount of time into the Amount to add field, then use the Unit of time dropdown menu to select the unit of time.
    • Calculate nth root of a number: calculate the root value of a number. Enter the root number into the Nth root number field.
    • Capitalize first letter: capitalize the first letter of the value. All other characters in the value will be changed to lowercase.  Only works for characters in the Latin alphabet.
    • Change all characters to lowercase: change all characters in the value to lowercase. Only works for characters in the Latin alphabet.
    • Change all characters to uppercase: change all characters in the value to uppercase. Only works for characters in the Latin alphabet.
    • Change date format: change the date format of the value.
    • Change to title case: change the value to title case. The first letter of every word in the value will be capitalized, all other characters will be changed to lowercase. Only works for characters in the Latin alphabet.
    • Convert to Unix timestamp: convert the value to a Unix timestamp.
    • Cut a character or several characters: remove specific characters from the value. Enter the characters that you want to remove into the Characters to cut field. For example, for the postal code 02145, you could enter 02 into the field, which would result in the value of 145.
    • Divide a number: divide the value by a specific number. Enter the number you want to divide by into the Number to divide by field.
    • Get absolute value of a number: returns the absolute value of a number. You can use this option to ensure that a number is positive.
    • Multiply by a number: multiply the value by a specific number. Enter the multiplier into the Number to multiply by field.
    • Remove HTML tags: remove HTML tags from a value.
    • Round a number: round the number either up or down. To select how many decimals are included in the formatted number, click the Decimal to round to dropdown menu and select a decimal. Then use the Rounding method dropdown menu to select whether you want to round up or down.
    • Subtract an amount of time: subtract an amount of time (e.g. hours, days) from the value. Enter the subtractor into the Amount to subtract field, then use the Unit of time dropdown menu to select the unit of time.
    • Test if a number is divisible: check whether a number is divisible by a specific value. Enter the number to divide by into the Number field.
    • Trim leading and trailing whitespace: remove whitespace from both ends of the value. 
  • Click the Output type dropdown menu, then select an output type. Depending on the formatting option you choose, data can be formatted as a text, number, date, or datetime value. 
  • To automatically save your formatted data to an existing property:
    • Select the Save formatted data to a property with a Copy property value action checkbox. Once your Format data action is saved, you will be automatically directed to a new Copy Property value action.
    • In the right panel, click the Property to copy "[formatted data]" to dropdown menu and select a property.
    • Click Save
  • After setting up your action, click Save.

Format your data with a custom formula

To format data with a custom formula:

  •  In the right panel, toggle on the Custom mode option.
  • In the Formula section, enter your custom formula.
  • Click Insert to add one of the following elements in your formula: 
    • Action output: the output value from an earlier action in the same workflow. 
    • HubSpot property: the property value belonging to the enrolled record. 
    • Function: an operator used to set up the formula, such as capitalizing the first letter or adding a number. 

  • To automatically save your formatted data to an existing property:
    • Select the Save formatted data to a property with a Copy property value action checkbox. Once your Format data action is saved, you will be automatically directed to a new Copy Property value action.
    • In the right panel, click the Property to copy "[formatted data]" to dropdown menu and select a property.
    • Click Save
  • A preview will automatically populate with an example result. If your formula uses a variable, like a property value, the preview will automatically show a result using default values. To see a preview with other examples, click Edit preview and enter a different test value for each field.
  • After setting up your action, click Save.

Using custom formulas, you can customize complex formatting options for your outputs. Learn more about the functions you can use with custom formulas. For example, you can calculate the following: 

  • Removing special characters from phone numbers: cut(cut(cut([phone], "-"), "*"), "#")

  • Calculating your sales representative's commission amount: ([Amount] * [commission_rate])

  • Concatenating strings values from different properties:  concat([company_name], [month_and_year])


Please note: while it is possible to copy and paste functions into the Formula section, any tokens used such as Action outputs or HubSpot properties must be manually inserted.

After you've added the Format data action, you’ll need to set up additional actions to use your formatted data. 

Use formatted data

After adding the format data action, you can either update an existing property with the formatted data or add the formatted data to your Google Sheet if you've installed the Google Sheets integration.   

Update existing properties with formatted data

Set up a Copy property value action to update properties with the newly formatted data. The action can use formatted data from any previous Format data action.

  • In the workflow editor, click the plus icon + to add a workflow action.
  • In the actions panel, select Copy property value.
  • In the right panel, click the Property or value to copy from dropdown menu, then click to expand a Format data action. To use the previously formatted value, select [output type] value.
  • Click the Target property type dropdown menu, then select the type of property that you want to copy the data into.
  • Click the Property to copy "[output type] value" to dropdown menu to select the property that you want to update. To create a new property, click Create a new [object] property.
  • Click Save.

With the Copy property value action set up, your workflow can now use previously formatted values to update CRM properties.

Add formatted data to Google Sheets

If you've installed the Google Sheets integration, you can add formatted data directly to your spreadsheet without updating any properties in HubSpot. For example, if the contacts' names in the sheet need to be formatted in lowercase, but you still want their names to remain capitalized in HubSpot. 

You can connect the Google Sheets app from the App Marketplace or from within a workflow.

  • In the workflow editor, click the plus icon + to add a workflow action.
  • In the actions panel, select Create Google Sheet row.
  • In the right panel, click the Spreadsheet dropdown menu. Then, select the spreadsheet you want to send data to. You can select from the spreadsheets that are owned by or have been shared with the user who connected Google Sheets to HubSpot. 
  • Click the Sheet dropdown menu. Then,  select the sheet you want to send data to. The Sheet field refers to the tabs at the bottom of your spreadsheet.
  • Set up your Google sheet rows: 
    • Click the Header dropdown menu. Then, select the corresponding columns in your Google sheet
    • Click the Property dropdown menu. Then, click to expand a Format data action. To use the previously formatted value, select [output type] valueThis action can use formatted data from any previous Format data action.
    • Repeat until you have completed your new row. 
  • Click Save

With the Create Google Sheet row action set up, your workflow can now use previously formatted values to add new rows in your Google Sheet without affecting existing properties.

Learn more about adding data to Google Sheets with workflows

Was this article helpful?
This form is used for documentation feedback only. Learn how to get help with HubSpot.