CSV Values Converter: How to Bulk Replace Values in a CSV Column

Your file has FR, DE, US in a country column. The destination expects France, Germany, United States. Or you have status codes (1, 2, 3) that should be labels. Or a French export with Ouvert, Gagné, Perdu going into an English CRM.

Find-and-replace handles the first three values. Then you realise the same column has 18 distinct codes, the next file will have 22, and you’ve been clicking through Excel’s replace dialog for half an hour.

A CSV values converter is the tool that turns this into one operation. You define what each value should become once, and apply it to every row, every column, every file.

How to convert values in a CSV column: the short version

The four steps for any bulk value conversion:

1. Audit the source column: sort and deduplicate to see every unique value currently in the file
2. Define the target values: the canonical list from the destination tool, your style guide, or the team that owns the field
3. Build the mapping as a table: one row per source variant, one column for the target value
4. Apply and save: run the substitution in one pass, then save the mapping with a name so next month’s file from the same source converts in one click

Each step gets covered in depth below, with the spreadsheet, script, and tool-based ways to handle them.

What types of CSV value conversions you’ll run into

Most CSV value conversion work falls into a few recognisable patterns:

• Code to label: country codes (FR → France), currency codes (USD → US Dollar), status codes (1 → Active, 2 → Inactive)
• Abbreviation to full name: state codes (CA → California), department codes (ENG → Engineering)
• Language translation: Rouge → Red, Ouvert → Open, Industrie technologique → Technology
• Boolean normalisation: 1/0, Y/N, Yes/No, True/False, Oui/Non collapsing into one consistent format
• Casing and punctuation: CLOSED-WON, closed won, closed_won all becoming Closed Won

Each of these is the same shape of problem. Source value X needs to become target value Y, across thousands of rows, often across multiple columns and multiple files.

If your conversions are specifically targeting a CRM picklist field (Lead Source, Industry, Deal Stage, etc.), the rules are slightly stricter. See how to standardise picklist values before a CRM import for the CRM-specific gotchas.

Why find-and-replace doesn’t scale for bulk CSV values

Excel’s find-and-replace dialog handles one mapping at a time. For a column with 5 unique values, it’s tolerable. For 30 values, it’s tedious. For multiple columns at once, it’s a guaranteed mistake.

The deeper issue is that the work is invisible. Once you’ve replaced FR with France, the file just shows France. There’s no record of what was changed, no way to verify you did it consistently, and no way to apply the same conversion to next month’s file without redoing the whole thing.

It also doesn’t handle variants. If half your file has FR and the other half has fr or France (trailing space), you have to remember to run replace for each, or wrap the column with LOWER() and TRIM() first. Each new variant means another pass.

Using VLOOKUP to convert values in a CSV column

A step up from find-and-replace is keeping the mapping as data instead of as actions. In Excel or Google Sheets, this looks like a two-column lookup table:

Source	Target
FR	France
DE	Germany
US	United States
UK	United Kingdom
IT	Italy

And then a VLOOKUP or XLOOKUP on the source column:

=XLOOKUP(A2, Lookup!$A:$A, Lookup!$B:$B, A2)

The fourth argument (A2) is the fallback if no match is found, which keeps the original value rather than throwing #N/A.

This is more maintainable than find-and-replace. The mapping is visible, editable, and you extend it by adding a row. But it still has limits.

The lookup is exact-match only. FR matches FR, but fr (trailing space) doesn’t. Wrapping in TRIM(LOWER()) helps but only handles whitespace and case, not typos or spelling variants.

The mapping doesn’t travel with the file. When you save and send the cleaned CSV, the lookup table stays behind in your spreadsheet. The next person who needs the same conversion starts from scratch.

And you still have one lookup table per column. A 10-column file with conversions on 6 columns means 6 lookup tables, 6 formulas, 6 things to maintain.

How to build a reusable CSV value converter

The pattern that holds up over time is treating the conversion as a saved asset, separate from any one file. Three properties matter for a reusable CSV value converter:

1. The mapping is named and reusable across files
2. Adding a new variant is one row in a table, not a formula edit
3. Unknown values are flagged for review rather than silently passed through or errored on

In practice, this looks like a tool where you upload your CSV, pick the column to convert, and either apply a saved mapping or build a new one. Every unique value in the source column shows up, you assign each to a target value, and the substitution runs in one pass. Save the mapping with a name, and the next file from the same source applies it in one click.

This pattern handles the boring 90% (the values you already know about) automatically, and surfaces the 10% (new variants you haven’t seen) for review.

Handling unmapped or unknown CSV values

The remaining gap with any mapping approach is what happens when a value appears that you didn’t anticipate. A new country, a typo (Frnace), an abbreviation you haven’t seen before, a French export when you’ve only ever processed English files.

Three ways to handle the unknowns:

• Pass through unchanged with a flag. The value stays as-is in the output, but you get a list of what wasn’t mapped, so you can either ignore it or update the mapping for next time.
• Local string normalisation. Trim whitespace, lowercase, collapse punctuation, and try the lookup again. This catches fr matching FR even if you didn’t add the variant explicitly.
• AI-assisted matching. For cases where the source string is genuinely different from any target (tech vs Technology, Rouge vs Red, Frnace vs France), a model can suggest the closest target value. You review the suggestion before it’s applied, so a wrong guess doesn’t silently corrupt the data.

The first option is always safe. The second is fast and works offline. The third handles spelling variants, language differences, and abbreviations you’ve never explicitly seen, without forcing you to enumerate every possible spelling.

If your CSV also has accented characters that look corrupted (café rendered as caf? or café), that’s an encoding issue rather than a value conversion one. The fix is in how to fix encoding errors in a CSV file.

Converting values across multiple CSV columns at once

Real files rarely have just one column to convert. A typical contact export might need:

• Country column: codes to full names
• Status column: numbers to labels
• Language column: ISO codes to language names
• Region column: abbreviations to full names

Doing each in isolation works, but it’s the same setup four times: open file, find column, build or apply mapping, save, repeat. A converter that handles multiple columns in a single configuration cuts that to one pass, and the saved configuration is what you reuse next month.

This same principle applies to other column-level cleanup tasks. If one of your “columns” is actually two values stuck together (a John Smith field that needs to become First Name + Last Name), that’s a column splitting problem rather than a value conversion one. If the values are dates with mixed formats, the rules are different again, see how to standardise date formats in a CSV.

CSV values converter checklist

Before running any bulk value conversion:

• List every column that needs value conversion, not just the obvious ones
• For each column, confirm the canonical target values from the destination tool, your style guide, or the team that owns the field
• Audit the source: sort and deduplicate the column to see every variant currently in the file
• Build the mapping as data (a table), not as actions (find-and-replace)
• Decide what happens to unmatched values: pass through, flag, or AI-suggested
• Save the mapping with a name so the next file from the same source applies it instantly

If you’re doing this regularly across multiple files or sources, Asphorem’s CSV Normalizer saves the mapping per column, applies it on upload, and uses AI matching to suggest targets for variants you haven’t explicitly mapped. New variants get reviewed once and added to the saved mapping, so each subsequent import gets faster instead of starting over.

CSV values converter: frequently asked questions

Can you bulk replace values in Excel?

Yes, but only one mapping at a time through the Find and Replace dialog (Ctrl+H). For multiple value substitutions in a single column, build a lookup table and use VLOOKUP or XLOOKUP against it. Both approaches break down once you need to handle typos, spacing variants, or apply the same mapping across multiple files.

What’s the difference between find-and-replace and VLOOKUP for converting CSV values?

Find-and-replace performs each substitution as a separate action and leaves no record of what was changed. VLOOKUP keeps the mapping as a visible two-column table and applies all substitutions in one pass, which makes it easier to audit, extend, and reuse within the same spreadsheet. Neither approach is portable across files: the mapping has to be rebuilt or copied each time.

How do I convert country codes to country names in a CSV?

Build a two-column lookup with ISO codes (FR, DE, US) and full names (France, Germany, United States), then apply it via VLOOKUP, a script, or a CSV value converter tool. For ISO 3166-1 alpha-2 codes, the canonical list has 249 entries and is freely available as a CSV from the ISO website.

Can AI convert values in a CSV automatically?

Yes, for cases where exact string match would fail. AI-assisted value matching can map tech to Technology, Rouge to Red, or Frnace to France even if those exact source variants aren’t in your mapping. Good tools surface the AI suggestions for review before applying, so you catch and correct any wrong guesses before they hit the output file.

How do I save a CSV value mapping to reuse on future files?

Excel and Google Sheets don’t have a built-in way to save a mapping outside of the workbook itself. To reuse a mapping across files, either copy the lookup table into each new workbook, write a script that references an external mapping file, or use a CSV cleaning tool that stores mappings independently and applies them on upload.

Can I convert values in multiple CSV columns at once?

In a spreadsheet, yes, but each column needs its own lookup table and formula. A purpose-built CSV value converter lets you configure mappings for every column in a single pass and apply them all together, which scales much better when files have 5+ columns needing conversion.