Skip to content
Workflows

Format your data with workflows

Last updated: September 17, 2021

Applies to:

Operations Hub Professional

The Format data workflow action enables you to automatically fix, format, and maintain your CRM data. For example, you can use this action to capitalize contact or company names, or calculate values based on properties. Data is formatted into values, which can then be copied into single-line or multi-line text properties

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 update that data.

1. 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.
  • 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 formatter 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 word. 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. To set up the Format field, use the list of directives in HubSpot's developer documentation
    • Change to title case: change the value to title case. 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 formatter option you choose, data can be formatted as a text, number, date, or datetime value. 
  • After setting up your action, click Save.

With the Format data action added, the workflow will be able to process and format data. You’ll then need to set up additional actions to use your formatted data. 

2. 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.

    format-data-add-second-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. The action can use formatted data from any previous Format data action. 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, set up your Google Sheet rows:
    • In the Spreadsheet dropdown menu, 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. 
    • In the Sheet dropdown menu, select the sheet you want to send data to. The Sheet field refers to the tabs at the bottom of your spreadsheet.
    • In each Header dropdown menu, select the columns to send data. 
    • In each Property dropdown menu, click to expand a Format data action. To use the previously formatted value, select [output type] value
  • 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