How to Standardize Inconsistent Company Names in a CSV

You exported a vendor list from your CRM. You want to know how much you're spending per company. Simple enough. Then you run a GROUP BY and get this:

• IBM - 23 transactions
• I.B.M. - 8 transactions
• International Business Machines - 4 transactions
• Microsoft - 31 transactions
• microsoft - 12 transactions
• MICROSOFT - 3 transactions
• Microsoft Corp - 7 transactions

That's 7 rows where there should be 2. Your spend-per-vendor chart is wrong. Your top-10 list is wrong. Every downstream calculation is wrong because the same company is split across multiple spellings.

This is one of the most common data cleaning problems. The data was entered by different people (or different systems) over months or years, and nobody enforced a standard format. Now you're stuck cleaning it up before you can do anything useful.

Here's how to fix it in ExploreMyData, step by step.

Step 1: Trim whitespace and normalize case

Before you start mapping variations to canonical names, knock out the easy wins. A surprising number of "duplicate" company names are just case differences or trailing spaces. "Microsoft " with a trailing space is not the same as "Microsoft" to a database.

Click the green + in the Pipeline panel and select Text Transform from the Transform group. Choose your company name column. Select trim and apply. Then open Text Transform again, same column, and select lowercase.

Text Transform does one transform per step, so those really are two steps and the pipeline shows two expressions, each rewriting the column in place:

TRIM(vendor_name)
LOWER(vendor_name)

If you were writing it as one expression it would be LOWER(TRIM(vendor_name)), with the step that ran first sitting innermost. Read function composition inside out and you'll never get the order backwards.

Two pipeline steps, and you've already collapsed Microsoft, microsoft, and MICROSOFT into one value. That knocks the Microsoft variations from 4 rows down to 2.

Step 2: Use Find & Replace for known variations

After normalizing case, you still have microsoft corp as a separate entry from microsoft. And i.b.m. is still separate from ibm.

Open Find & Replace from the Transform group. Select the vendor name column, enter microsoft corp in Find and microsoft in Replace. Apply.

Do the same for the IBM variations. Replace i.b.m. with ibm, and international business machines with ibm.

Each step generates a REPLACE() call:

REPLACE(vendor_name, 'microsoft corp', 'microsoft')

Notice what that is: a substring replacement. Find & Replace matches anywhere inside the cell unless you tick Entire cell, which switches it to a CASE WHEN vendor_name = '...' exact match. For suffix stripping, substring is exactly what you want. For mapping one whole company name to another, it usually isn't. Replacing "ibm" with "International Business Machines" as a substring turns "ibm cloud" into "International Business Machines cloud" and mangles anything with those three letters in the middle. Tick Entire cell, or use Bulk Replace, which always matches whole values.

The other default to know: Case sensitive is on. After step 1 everything is lowercase so it doesn't bite here, but on a raw column it means "Microsoft Corp" and "microsoft corp" are two separate passes.

This works well when you have a handful of known variations. But if you've got dozens of companies with multiple spellings each, doing them one at a time gets tedious. That's where Bulk Replace comes in.

Step 3: Bulk Replace for large-scale mapping

When you need to map many values at once, Bulk Replace is the better tool. It lets you define a set of source values and a single target value in one operation.

Open Bulk Replace from the Transform group. Select your vendor name column. For the first group, set the target to ibm and add i.b.m. and international business machines as source values.

Under the hood, ExploreMyData generates a CASE WHEN expression:

CASE WHEN CAST(vendor_name AS VARCHAR) IN ('i.b.m.', 'international business machines') THEN 'ibm' ELSE CAST(vendor_name AS VARCHAR) END

You can add as many groups as you need in a single operation. Map all the Salesforce variations (salesforce.com, sfdc, salesforce inc) to salesforce. Map all the Amazon variations. Do it all in one step.

How to spot the variations in the first place

The hardest part isn't fixing the names. It's finding all the variations, and the app will do a chunk of that for you. In the same Bulk Replace panel, switch from All Values to Similar Suggestions. It runs DuckDB's editdist3, the Levenshtein edit distance, across the distinct values in the column, lowercased, and groups anything within a single character edit. Each proposed group gets an + Accept button that drops it into your Groups list ready to apply.

One edit is a strict threshold, and that's the right default for company names: loosen it and you start merging Accenture with Adventure. It'll catch "salesfroce" and "salesforce", or "Nothwind" and "Northwind". It won't catch "i.b.m." against "ibm", which is two deletions, or "international business machines". Also worth knowing: it samples up to 500 distinct values, so on a long vendor tail it's showing you a slice.

For everything the suggestions miss, the Column Explorer is the next stop. Click the vendor name column header and you get every distinct value with its count, sorted by frequency. Companies with suspiciously low counts are usually misspellings or alternate names of a higher-count entry. If "microsoft" has 31 rows and "msft" has 2, that's a strong hint. Reading that list top to bottom is the most reliable way to build your Bulk Replace groups.

A strategy that scales

For a file with 10-20 companies, the three-step approach above takes about five minutes. For files with hundreds of unique company names, you'll want a strategy:

1. Trim and lowercase first. Always. This alone can cut your unique count by 20-30%.
2. Strip common suffixes. Use Find & Replace to remove " inc", " corp", " llc", " ltd" from the end of names. That collapses "microsoft corp" into "microsoft" without needing to know every company in the file.
3. Bulk Replace the rest. After the automated cleanup, group what's left and map manually.

Every step is in the pipeline

Each operation you apply shows up as a step in the pipeline panel with its generated SQL visible. Delete a step you got wrong and everything downstream rebuilds against the new result, so a bad Bulk Replace group costs you one click, not a re-import.

The Bulk Replace groups are the part worth keeping. The next vendor export will be just as messy, and probably messy in the same way, so the mapping you built once keeps paying out.

Standardize your vendor list →