How to Split a Column in a CSV Before Importing to Your CRM

Your contact export has a Name column. Every row has “John Smith” or “Marie Dupont” as a single value. HubSpot wants firstname and lastname as separate columns. Salesforce wants FirstName and LastName. Pipedrive accepts a full Name field, but your segmentation filters won’t work properly if first and last name are stuck together.

Splitting a column before import is one of those tasks that looks trivial until you actually do it at scale.

Why CRMs need fields split

CRM systems store first name and last name as separate properties because they use them separately: personalisation tokens in emails, alphabetical sorting, deduplication logic. A contact stored as Full Name: John Smith can’t be addressed as “Hi John” in a HubSpot email sequence. It also can’t be sorted by last name or deduplicated against an existing firstname: John, lastname: Smith record.

The same principle applies beyond names:

Address fields: Many exports have a single Address column. HubSpot, Salesforce, and Pipedrive all store City, State, Postal Code, and Country as separate fields.
Compound codes: An export might have Product-Size as a single column (T-SHIRT-XL) where the CRM expects Product and Size separately.
Owner + region: A field like Sales Rep / Territory that needs to become two separate lookup fields.

In all cases, the split happens before upload, not inside the import wizard.

Splitting a name column in Excel

Text to Columns (fastest for simple cases)

If every name in your file follows the same pattern (First Last, no middle names, no prefixes), Text to Columns is the fastest option.

Select the column containing full names
Go to Data → Text to Columns
Choose “Delimited” → Next
Check “Space” as the delimiter → Finish

Excel splits the column at every space. The result goes into the selected column and the column immediately to its right, so make sure that column is empty first.

This works cleanly for “John Smith” style names. It breaks immediately when names have more than one space.

Formula approach for more control

For files where you need to handle names individually, formulas give you more control.

Extract first name (everything before the first space):

=LEFT(A2, FIND(" ", A2) - 1)

Extract last name (everything after the first space):

=MID(A2, FIND(" ", A2) + 1, LEN(A2))

These work for “First Last” patterns. For Excel 365 and Google Sheets, TEXTSPLIT is cleaner:

=TEXTSPLIT(A2, " ")

TEXTSPLIT splits into multiple cells across columns automatically, which means you only need one formula per row rather than two. In Google Sheets, SPLIT(A2, " ") does the same thing.

After applying the formulas, paste-special-values over the result columns before deleting the original. If you don’t strip the formulas, the cells will show errors as soon as you remove the source column.

Edge cases that break these formulas

Middle names and initials

“John Michael Smith” splits into three parts, not two. LEFT gives you “John”. MID gives you “Michael Smith”. Your lastname column now contains the middle name and last name combined.

Options:

If middle names are rare, spot-check and fix manually after the split
If middle names are common, use a TEXTSPLIT result and manually assign columns
If middle initials appear (John M. Smith), decide whether to drop them or route them to a dedicated field

Prefixes and suffixes

“Dr. Sarah Connor” gives you Dr. as first name and Sarah Connor as last name. “James Earl Jones Jr.” splits into four parts.

There’s no formula that handles these correctly without a case-by-case rule set. If your file has a mix of prefixes and suffixes, the safest approach is to split, then filter and fix the rows that have more than two parts.

Single-name entries

Some contacts have only one name (particularly from international datasets or event registrations). FIND(" ", A2) throws a #VALUE! error when there’s no space. Wrap with IFERROR:

=IFERROR(LEFT(A2, FIND(" ", A2) - 1), A2)

This returns the whole value as first name when there’s no space, so you can spot and handle those rows separately.

Splitting other column types

Address fields

Addresses are harder because the structure varies by country and by how the data was entered. There’s no reliable formula approach for parsing a free-text address into City, State, and Postal Code.

If your source data has a structured address (e.g., a form that collected each part separately but exported them concatenated), and the separator is consistent (comma, pipe, semicolon), Text to Columns or TEXTSPLIT with that separator will work.

If the address is truly free-text, you need to handle it manually or accept that the split will be imperfect.

Compound codes

Codes with a consistent separator (T-SHIRT-XL, SKU-001-RED) split cleanly with Text to Columns or TEXTSPLIT(A2, "-"). Specify how many parts to expect, and verify a sample before applying across the whole column.

Using Asphorem’s split column feature

Asphorem’s CSV Importer has a split column feature built into the mapping step. You select the source column, choose the separator (space, comma, hyphen, pipe, or custom), and a live preview shows exactly how each value will be divided. Each segment maps to a separate output column.

The advantage over the formula approach: you see the result before committing, the mapping is saved with the rest of your column configuration, and the next time the same file format arrives the split is applied automatically without re-doing the formula work.

Edge cases (middle names, single-name entries) still need a human decision, but the base split happens in one step rather than across multiple formula columns.

Before you import: the checklist

Identify all columns that need splitting (name, address, compound codes)
Check for edge cases: multi-word names, prefixes, suffixes, single-name entries
Choose your split method: Text to Columns for uniform data, formulas for cases that need control
Strip formulas before deleting source columns (paste-special-values)
Verify the result on a sample before applying to the full file
Confirm the split output column names match what the CRM expects

Splitting CSV columns: frequently asked questions

How do you split a name column into first and last name in Excel?

For uniform “First Last” data, use Data → Text to Columns → Delimited → Space. For more control, use =LEFT(A2, FIND(" ", A2) - 1) for first name and =MID(A2, FIND(" ", A2) + 1, LEN(A2)) for last name. In Excel 365 and Google Sheets, TEXTSPLIT(A2, " ") returns both in one formula.

What’s the difference between TEXTSPLIT and Text to Columns?

Text to Columns is a one-off action that overwrites the source column with split parts. TEXTSPLIT is a formula that recalculates whenever the source changes and produces multiple cells from a single formula. Use Text to Columns for cleanup, use TEXTSPLIT for files that get refreshed and re-split repeatedly.

How do you handle middle names when splitting a name column?

LEFT/MID formulas put the middle name into the last name column. Either filter rows with more than one space and fix them manually, or use TEXTSPLIT and assign columns case by case. There’s no formula that handles this perfectly without business rules.

Can you split a full address into street, city, and postal code automatically?

Only if the source data was structured before being concatenated and uses a consistent separator (comma, pipe, semicolon). Free-text addresses can’t be reliably parsed by formulas because the structure varies by country and by how the data was entered.

How do I split a CSV column without breaking the destination columns?

Apply the formula in a helper column, paste-special-values over the result, then delete the source column. If you delete the source while a formula still references it, every result cell shows #REF!. The order matters: convert formulas to values first, delete second.