How do I separate a survey export’s combined name field into usable columns?

First decide what the columns are supposed to mean—not just where the text should break. For example, are you trying to create “Given name” and “Family name,” or simply “Text before the first space” and “Remaining text”? Those are not always equivalent: a name may contain several words, and the field alone may not tell you which word is a family name.

Keep an untouched copy of the export or original name column, then inspect a representative sample. Note the patterns you actually see: one word, two words, three or more words, extra spaces, or a missing part. If the survey instructions defined how people should enter names, use that as context, but don’t treat it as proof that every response followed the format.

Work in a copy or direct the split into new columns, leaving the original field intact. In Excel, Text to Columns lets you choose a delimiter, preview the division, and specify a destination before finishing [S2]. Power Query also offers splits at the left-most delimiter, right-most delimiter, or every occurrence, plus a maximum number of columns [S1].

Choose based on the pattern you observed. Splitting at the first space means “first part” and “everything after the first space”; it does not prove those parts are given and family names. Splitting at every space can create more columns than you intended when an entry has multiple words. Check the preview against examples with one, two, and several words before applying a rule to the full field. The Microsoft documentation describes these splitting options; it does not determine the correct interpretation of anyone’s name.

Don’t force an uncertain entry into a guessed interpretation. Use a review flag or a separate exceptions list, and keep each exception linked to its original row using an existing response ID or row number. Examples to review include a blank value, a single word, several words, repeated spaces, or text that the preview places unexpectedly. These are review prompts, not proof that a response is wrong.

For instance, if a working sample contains “Mina Lee,” “Mina van Lee,” and “Mina,” a first-space split would put “Mina” in the first output column for all three, but the remaining text differs. Whether “van Lee” is one family name, or what a single-word response represents, cannot be settled by splitting alone. Preserve the exact original spelling and spacing in the source column; make any cleanup or interpretation in separate working columns, and record the rule you used.

Use this sequence:

1. Save an unchanged copy of the export and retain the original combined field.
2. Inspect a sample and write down the patterns and exceptions you see.
3. Name the intended output columns carefully; avoid labels that claim more than the data tells you.
4. Choose a delimiter and split position that match the observed format. Preview results and set the destination to new columns [S1][S2].
5. Compare the new columns with the originals, then review flagged irregular entries manually rather than guessing.

Example of a simple review log: Row ID | Original name | Split result | Review reason | Decision [ID] | Mina van Lee | Mina | van Lee | Multiple words; confirm intended fields

This is a tracking template, not a rule for interpreting that example. Splitting tools can divide text according to a chosen delimiter; the cited guidance does not establish a universal name format or decide how ambiguous responses should be classified.