)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
HubSpot requires standardized two-letter state codes for contact imports, but your data likely contains full state names like “California” or “Texas.” Converting hundreds of state names manually is time-consuming and error-prone.
Here’s how to automate state name conversion using spreadsheet formulas before uploading to HubSpot.
Convert state names to abbreviations using Coefficient
CoefficientHubSpotHubSpotlets you import contact data into spreadsheets, apply bulk conversion formulas, then export clean data directly toor. This eliminates validation errors by ensuring state codes meet requirements before upload.
How to make it work
Step 1. Create a state conversion lookup table.
Set up two columns in your spreadsheet: one with full state names (California, Texas, New York) and another with corresponding abbreviations (CA, TX, NY). Include all 50 states plus territories like Puerto Rico (PR) and Washington DC (DC).
Step 2. Import your contact data using Coefficient.
Connect your data source through Coefficient’s Connected Sources menu. This could be a CSV file, database, or another system. Your contact data will populate in the spreadsheet with the original state names intact.
Step 3. Apply the VLOOKUP conversion formula.
In a new column next to your state data, use this formula: =VLOOKUP(B2,StateTable,2,FALSE). Replace “B2” with your state column and “StateTable” with your lookup table range. This automatically converts “California” to “CA” and “Texas” to “TX”.
Step 4. Use Formula Auto Fill Down for batch processing.
Coefficient’s Formula Auto Fill Down feature automatically applies your conversion formula to new rows when data refreshes. This means future contact imports will convert state names without manual intervention.
Step 5. Export cleaned data to HubSpot.
Use Coefficient’s INSERT functionality to upload your contacts with properly formatted state codes directly to HubSpot. The data bypasses validation errors because state abbreviations are already standardized.
Save time with reusable templates
Get startedThis approach transforms a recurring manual task into an automated process. Create the conversion template once, then reuse it for all future contact imports from publishing partners or other sources.with Coefficient to eliminate state formatting headaches.
