Split text into columns

I received a teacher roster in one column where each cell has LastName, FirstName — Grade — Email. Split that column into four adjacent columns: LastName, FirstName, Grade and Email for the entire sheet, keeping the original column intact. Make sure entries with missing commas still fall into correct columns when possible.

Before I hand this roster to the attendance system owner, tidy the split so names are usable: place LastName and FirstName in separate columns, remove stray spaces and any trailing punctuation, convert Grade to numeric where possible, and flag rows missing an email. Show a quick validation count of malformed rows at the top.

I imported a CSV and some rows use commas, others use semicolons and some have no consistent delimiter. I can’t re-export from the source. Should I split by comma first then clean separators, or try a single split on the most common delimiter and fix exceptions manually? What will catch the most errors fastest without losing data like 'Smith, Jr.' or emails with commas?

Every term I spend hours splitting and fixing teacher and parent lists with inconsistent delimiters and stray titles. Where am I failing — process at the source, or my import step — and what two small rules or checks will stop this recurring cleanup? Suggest a pre-upload template, a simple validation check, and one communication line to send to staff to reduce my workload.