How to split a messy analytics column by hand

A web analytics export can put several values in one column, such as a page path, traffic source, and campaign name joined together. If the pattern is consistent, you can split the values by hand without changing the original export.

Use the CSV Editor to inspect the file, find repeated patterns, and edit entries. First make a separate working copy so you can compare your changes with the source.

Split the column by hand

1. Open the working copy in the CSV Editor. Find the crowded column and scan several rows, including rows near the beginning, middle, and end of the file. Look for the exact character or text that separates the values.
2. Write down the fields you want to keep and their order. For example, a cell containing page=/plans | source=paid | campaign=spring could become three columns named page, source, and campaign.
3. Create destination columns for those fields, keeping the original crowded column for now. If your editing view does not let you add columns, use the CSV Editor to inspect and correct the values, then prepare a new CSV with the same headers and rows in a suitable spreadsheet or text editor.
4. Work through the rows one at a time. For each cell, separate the components at the delimiter you identified, then place each value in the matching destination column. Keep the original spelling, capitalization, and punctuation unless you have a specific reason to normalize them.
5. Check the end of the file and compare several completed rows against their original cells. Confirm that every piece of data landed under the right header, then save the edited copy as a CSV.

For a small export, manual entry is easy to verify. For thousands of rows, it is slow and increases the chance of a missed value. If the delimiter or field order changes from row to row, resolve that pattern before continuing, rather than guessing as you go.

What the values tell you

The same separator appears in every row

A repeated separator, such as | or a semicolon, gives you a reliable place to divide the text. Check that it is not part of a value itself. A campaign name could contain a vertical bar, for example, making a simple visual split ambiguous.

Some rows have fewer fields

A missing value is not necessarily an error. Leave the corresponding destination cell blank if the export has no campaign or source for that row. Do not shift later values to the left to fill the gap, because that would put them under the wrong headers.

Some rows contain extra separators

Check whether the separator is inside a value or marks another field. For URL parameters, an ampersand often separates parameters and an equals sign separates a parameter name from its value, as in ?utm_source=paid&utm_campaign=spring. A URL may also contain encoded characters or parameter values that include punctuation, so inspect the whole row before deciding how to split it.

The CSV looks shifted after saving

A comma inside a value can make a row look like it has extra fields if the value is not quoted correctly. In a CSV, a value containing a comma is commonly enclosed in double quotes, and a double quote inside that value is written twice. Reopen the saved file and check that its columns still line up.

Fix common splitting mistakes

1. Keep an untouched copy. Save a duplicate before editing. If a value gets split incorrectly, compare it with the source and restore the complete original cell instead of trying to reconstruct it from memory.
2. Choose one parsing rule. Decide whether you are separating at a pipe, a semicolon, or a URL parameter boundary. Apply the same rule to every matching row, and do not split at punctuation that also appears inside the values.
3. Match values to headers, not position alone. If entries include labels such as source= and campaign=, use those labels to identify each value. A row may list the fields in a different order from the row above it.
4. Check blanks and duplicates before importing. Look for an empty value where you expected one, repeated delimiters, and fields that appear twice. Keep blanks when the source is genuinely missing data, and resolve duplicates only when you know which value is correct.
5. Reopen the saved CSV. Inspect the edited file again in the CSV Editor. Check a row with commas, quotes, or a long URL as well as an ordinary row. This can catch quoting problems that are easy to miss while editing.

Keep the export useful for analytics

Preserve the raw column until you have verified the new fields. It gives you a reference for troubleshooting and helps you explain how the cleaned columns were produced. You can remove the raw field later if the destination system does not need it.

Be careful when splitting URL query strings. In ?utm_source=paid&utm_campaign=spring, the question mark begins the query, the ampersand separates parameters, and the equals signs separate names from values. If a value is URL-encoded, characters may appear as codes such as %20. Keep those codes as they are unless you intend to decode them, because changing a value can affect matching and reporting.

The CSV Editor works in your browser, and files you open stay on your device.