Format Names, Phone Numbers, Addresses Using the Transform Data Module

Navigate to Data Management > Transform Data, pick a record type, and explore the default templates for a pre-built solution.

Under 1. Filter Records, adjust the settings to filter the CRM records down to those you would like to format. This ensures you aren't trying to format fields that don't contain any data. And it is best practice to only tackle one field at a time.

The example below will search for contacts with the first name in all caps:

• Field: First Name 
• Condition: regex
• Value: [A-Z]{3,}

This regular expression is looking for:

• Uppercase letters from A to Z: [A-Z]
• Three or more occurrences of these uppercase letters: {3,}

You can add any filter relevant to your use case—lifecycle stages, record creation dates, engagement triggers, etc. Insycle can use any data in your CRM in the filter step.

Click the Search button and scroll down to the Record Viewer at the bottom of the screen. All records that match your filter will appear. You can alter the fields that appear in this preview from the Layout tab in 1. Filter Records.

Under 2. Configure Changes, give Insycle instructions on what formatting and cleanup changes to make to the identified records.

Select the Field that contains the value you want to start with, then select the Function and enter the parameters. Click the plus at the end of the row to add an additional function for this field.

Each function uses the value output from the previous function, so the sequence matters.

Pre-built functions standardize phone numbers, countries, states, domains, and other values, so explore the options in the Function dropdown or review the Function Catalog.

In the above example, these standardizations are being made to the First Name in each contact record:

• Remove whitespace from before or after the value.
• Remove salutations such as Mr or Mr. or Mrs or Mrs. 
• Capitalize the first name value.

The next example will format specific parts of the Street Address field values. The "Map: Terms" Function will look for the Existing Text and, if found, will change it to the New Text value. Here in the Existing Text fields, the bar character "|" between the values means OR, as in, look for "street" or "st.".

Altogether, these rules will standardize the Street Address like this:

• Street → St
• Drive → Dr
• Avenue → Ave
• Boulevard → Blvd

Preview Changes in the CSV Report

With the filters and functions set up, you can preview the changes. It's important to verify that your formatting works as expected before pushing changes to your live database.

Under 3. Review, click Review, then select Preview.

On the Notify tab, add any additional recipients who should receive the CSV (and make sure to hit Enter after each address). You can add additional context to the subject line and email body.

On the When tab, click Run Now and select which records to apply the change to (you could do All, but if you have a large number of records, you may just want to do a chunk for your preview), then click the Run Now button.

Open the CSV file from your email in a spreadsheet application and review the values for each row.

The Result column indicates the outcome of the operation for each record. The possible results are:

• Updated - The transform was applied successfully.
• Failed - The transform could not be applied. See the Message column for details.
• Unmodified - The transform was not applicable to the record.

On the far right, you can see the (Before) and (After) values side by side; this example shows First Name (Before) and First Name (After).

If the results don't look as you expected, go back to your filters in 1. Filter Records and functions in 2. Configure Changes, and try making some adjustments. Then, preview again.

Apply Changes to the CRM

If everything in your CSV preview looks correct, return to Insycle and proceed with applying the changes to the live CRM data.

Under 3. Review, click the Review button again, and this time select Update mode.

On the When tab, you should use Run Now the first time you apply these changes to the CRM. If you have a large number of records, you may want to do a smaller batch to review the results in your CRM.

After you've seen the results in the CRM and you are satisfied with how the operation runs, you can save all of the configurations as a template and set up automation so this formatting operation runs on a set schedule.

If you have several templates you'd like to run together automatically, you can create a Recipe. Additionally, HubSpot users can integrate Insycle Recipes into HubSpot Workflows.

By automating with a template, you'll ensure your fields are consistently and automatically formatted over time. 

You can combine functions to isolate parts of a value and apply formatting before copying to a destination field.

Extract First and Last Name from Email Address

You can look for email addresses that follow the "firstname.lastname@domain.com" format, then combine functions to parse and extract potential first and last name values from email addresses, format them properly with capitalization, and populate the respective First Name and Last Name fields accordingly.

You can use the built-in template, Extract First and Last Name from Email Address, as a starting point.

In this example, the filter under 1. Filter Records is set up to find records that match all of the following criteria:

• First Name field is empty
• Email field contains a period "."
• Email field matches the regular expression pattern "[a-z]{2,}.[a-z]{2,}", which matches email addresses with at least two lowercase letters before and after the "." separator
• Email field does not match the negative regular expression pattern "[0-9@#$%^&()+-:].*", which excludes email addresses containing numbers, @ symbols, or certain special characters
• Email field does not contain the string "info|marketing|admin|sales[...]" (and many other terms), which  filters out common role-based email addresses

Under 2. Configure Changes, the functions are set up as follows:

• The first function splits the Email field value by the "." delimiter and selects the 1st term from the split. This extracts the text before the first delimiter in an email address, typically the username or first-name portion.
• The extracted 1st term from the Email field is then formatted using the "Proper case person" format function, which capitalizes the first letter of each word (e.g., "johnsmith" becomes "John Smith").
• The formatted value is then copied into the First Name field.
• The second function splits the Email Username field value by the "." delimiter and selects the 2nd term from the split. This extracts the text after the first delimiter in an email address, which is often the last-name portion.
• The extracted 2nd term is formatted using the "Proper case person" format.
• The formatted value is copied into the Last Name field.

If you have a CSV file containing data to be imported as new records, the Data Preparation step in the Magical Import module can format names, phone numbers, addresses, and other text fields before the data is written to your CRM. It uses the same functions found in the Transform Data module. Data Preparation works only on the CSV values shown in the Preview, so it does not read or change data already in your CRM.

For step-by-step instructions, see Format Names, Phone Numbers, and Addresses in CSV Data before Importing.

If there isn’t a single function that makes the change you’re looking for, you can still complete the step in one template.

You can layer functions to make changes in a series of steps. They apply to the field value cumulatively, executing from top to bottom.

For example, you can use two functions to clean up Website URL values with multiple directories. Instead of “https://app.insycle.com/data/bulk/contact/,” you just want to keep the main domain value, “insycle.com":

1. The Extract: Domain from URL function removes https:// and the subdomain, but the directory values after the domain, '/data/bulk/contact/', remain. 
2. To get rid of those directory values, add the function Split: By any delimiter and keep Nth term to remove everything after the forward slash, “/”, and keep the value in the first section.

For another example, you can use several functions to populate the country value by using the email address:

1. Select the Email field with the value to start with.
2. Select the Split: By any delimiter and pick the last term function. 
3. In the Parameter field, enter a "." to use as the delimiter, telling Insycle which part of the email value to isolate and use for the next steps.
4. Click the plus at the end of the row to add another function for this field.
5. Now that you have the two-character country code, select the Standardize: Country Code2 to Name function to transform it to the full country name. Click the plus.
6. Select the Copy: Value function and the Target Field the country name should be written into.

HubSpot has two distinct phone number features that affect how Insycle interacts with phone number data: phone number validation and dynamic phone number formatting. Understanding how these work will help you configure Insycle correctly and avoid formatting issues.

Phone Number Validation Setting

HubSpot's phone number validation setting can affect how Insycle formats phone numbers on HubSpot records. When this setting is enabled in HubSpot, the HubSpot API accepts phone numbers only in the international standard E.164 format—a plus sign, country code, and digits only, with no spaces or separators (for example, +48879878765). If Insycle attempts to write a phone number in any other format while this setting is enabled, HubSpot will reject the update and return an error.

Error messages you may see:

• Underlying error message from HubSpot: Property values were not valid: [{"isValid":false,"message":"Number must match format '+18884827768' or '+18884827768 ext 123'.","error":"INVALID_PHONE_NUMBER","name":"validated_phone_number"}]
• Underlying error message from HubSpot: Property values were not valid: [{"isValid":false,"message":"Enter a valid country code.","error":"INVALID_PHONE_NUMBER","name":"validated_phone_number"}]

If you're seeing either of these errors, you have two options to resolve it:

1. Disable phone number validation in HubSpot — With validation turned off, HubSpot will accept phone number formats from Insycle as before.
2. Keep validation enabled and use E.164 formatting — If you prefer to keep HubSpot's validation on, configure Insycle to format phone numbers exclusively in E.164 format so that every update matches what HubSpot's API requires.

You can find more details in HubSpot's Set up phone number property validation article.

Dynamic Phone Number Formatting

In HubSpot, phone number fields that use the "number formatting" feature are dynamically displayed in the regional format appropriate to that phone number, even though the underlying stored value is different. In Insycle, that same field's data appears in the plain E.164 format ("+xxxxxxxxxx") rather than the region-specific format shown in HubSpot.

To standardize phone numbers, format all phone numbers using the E.164 standard in Insycle. Once the value is written back to HubSpot, HubSpot's "number formatting" feature will automatically display the phone number in the region-specific format for that number's country, without any additional configuration needed in Insycle.