Merge CSV Columns

Merging columns joins several fields into one: first and last name into a full name, street and city and postcode into an address, two codes into a lookup key. Pick the columns, pick what goes between them, and the merged column appears beside the sources. Blank values are skipped by default so you never get a double separator. It runs entirely in this tab.

Need to combine two files rather than two columns? The CSV merger does that.

The double space nobody notices until it matters

Take a contact list with first, middle and last. Join them with a space and everybody who has no middle name comes out as Ada Lovelace, with two spaces where the missing value was.

On screen that looks like nothing. Then the column becomes a mailing label, and the label looks wrong. Then somebody uses it as a match key against another system that wrote the name with one space, and the join finds nothing. Then somebody sorts by it and the double-spaced entries cluster in an odd place. It is a small bug with a long tail, and it is the reason Blank values is set to Skip them before you touch anything.

The other setting is there because sometimes the gap is the point. A fixed-shape identifier built out of three codes needs its empty slot to stay empty, or the parts stop lining up. Switch to Keep the gap and every column contributes something, even if that something is nothing.

Worked example: names, with and without the gap

A three-row file, people.csv:

first,middle,last
Ada,,Lovelace
Mary,Kenneth,Keller
Grace,,Hopper

Tick all three, leave the separator as a space, name the result full_name, and take the default blank handling:

first,middle,last,full_name
Ada,,Lovelace,Ada Lovelace
Mary,Kenneth,Keller,Mary Kenneth Keller
Grace,,Hopper,Grace Hopper

Now switch Blank values to Keep the gap and the same file gives:

first,middle,last,full_name
Ada,,Lovelace,Ada  Lovelace
Mary,Kenneth,Keller,Mary Kenneth Keller
Grace,,Hopper,Grace  Hopper

Two spaces on rows 1 and 3. The page notices and says so above the table: 2 rows had a blank in at least one of the merged columns, so the separator appears twice in a row there. Whichever setting you chose, you are told what it did.

Where the merged column lands

Not at the far right, which is where most tools put it. A file whose name parts sit in columns two, three and four gets its full_name in column five, immediately after the last source column. If you tell the tool to remove the sources instead, the merged column takes the position of the first one, so the shape of the file is preserved and a downstream script that reads by position has a fighting chance.

The other ordering decision worth knowing: values are joined in header order, not in the order you ticked the boxes. Ticking last, then first, still gives you Ada Lovelace rather than Lovelace Ada. This is deliberate. Checkbox order is invisible once you have clicked, so a result that depended on it would be impossible to reproduce or explain. If you need the other order, reorder the columns first, or use a calculated column, where last & ', ' & first says exactly what you mean.

A name collision is resolved rather than ignored. Asking for a merged column called notes when the file already has a notes gives you notes_2. Nothing in the source is overwritten by accident.

Separators that are not a space

Nothing at all. Clear the box and the values run together. This is the setting for building a compound key out of two codes, where any separator is one more character for the other system to disagree about.
A comma and a space. Type , including the trailing space. Handy for city, country. The resulting cell contains a comma, which is quoted correctly on the way out, so the CSV stays valid.
A pipe or a semicolon. Common when the merged column is going to be split again later, by you or by somebody else's importer. Pick a character your data does not contain.
A tab. Type \t. Backslash escapes for tab, newline and carriage return are understood, because there is no other way to type them into a one-line box.

Each value is set to Trim it by default, so a source cell padded with spaces does not smuggle them into the middle of the merged value. Turn it off when the padding is meaningful, which happens with fixed-width identifiers and almost nowhere else.

Before you merge, and after

Two things are worth doing first if the merged column is going to be used as a key rather than read by a person.

Run the whitespace cleaner over the source columns. Trimming here handles padding at the ends of each value, but it will not collapse a double space inside one, and it will not remove the zero-width characters that arrive when data has been pasted out of a web page. Those are invisible, they survive the merge, and they are the single most common reason two keys that look identical refuse to match.

Then think about case. Acme Inc and acme inc merge into two different keys. The case converter will put a column into one form first, or you can leave it and let the fuzzy matcher sort it out afterwards.

Afterwards, the stats strip tells you whether anything went wrong: how many rows merged to nothing because every source cell was empty, and how many had a blank in at least one column. Both counts are worth a glance before you download, because both of them mean something in the source data that you probably did not know about.

Frequently Asked Questions

Does the order I tick the boxes matter?

No. Columns are joined in the order the file has them, so ticking last and then first still gives you the first name in front. Checkbox order is invisible after the fact and a result that depended on it could not be reproduced. For an order that differs from the file's, use a calculated column and write the expression out.

What happens when one of the columns is empty?

By default that value is skipped, so three columns joined with a space where the middle one is blank give Ada Lovelace with one space rather than two. Switch Blank values to Keep the gap and the empty field contributes an empty string, which produces the double separator. The stats strip counts the affected rows either way.

Can I merge with no separator at all?

Yes. Clear the separator box and the values run straight together, which is what you usually want when the merged column is a compound key rather than something a person reads.

How do I use a tab as the separator?

Type \t. Backslash escapes for tab, newline and carriage return are understood, since a one-line text box cannot take those characters directly.

Where does the new column appear?

Immediately after the last of the source columns when the originals are kept, and in the position of the first source column when they are removed. Not at the far right, so the shape of the file survives.

What if a column with my chosen name already exists?

It is not overwritten. A merged column named notes in a file that already has notes becomes notes_2, and the stats strip names the column it actually created.

Will the merged value break my CSV if it contains a comma?

No. The output is written with proper quoting, so a merged cell holding city, country is one field and reads back correctly in any spreadsheet or parser.

Is anything uploaded?

No. The file is read, merged and rewritten by JavaScript in this browser tab. There is no upload endpoint on the page and nothing is kept between visits.

Several columns, one clean value

Free, no account, no upload. Tick the columns, set the separator, check the preview, take the CSV.

Back to the merger