To split a column in Excel on a Mac, select its cells, open Data > Text to Columns, choose the delimiter that separates each field, check the preview, and finish with a destination that has enough empty space. For repeatable, formula driven splits in Microsoft 365 or Excel 2024, use TEXTSPLIT instead. The key is to match the method to your data and protect neighboring columns before applying it.

Prepare the worksheet before splitting

Make a copy of the sheet or workbook if the original column is important. Text to Columns distributes results into adjacent cells, and Microsoft warns that existing data there can be overwritten. Insert enough blank columns to the right of the source for the widest row in your data. A comma separated list may need two columns; an entry with several separators may need more. Microsoft’s instructions for distributing cell contents also specify selecting a range that contains only one source column.

Check a few representative rows and identify the actual separator: comma, tab, semicolon, space, or another character. If your rows are separated by commas but some values also contain commas—such as 42 Main St, Apt 3—splitting at every comma may put part of one field into an extra column. Decide whether you need a different delimiter or a method that respects quoted values before proceeding.

Use Text to Columns in Excel for Mac

  1. Select the cells in the source column, excluding the header if you want to keep it unchanged. Select only that column’s range; avoid including adjacent fields.
  2. On the Data tab, choose Text to Columns. Microsoft documents this command for splitting a selected cell, range, or column. Ribbon group labels and layout can vary by Excel version, so use the Data tab command rather than relying on a Windows specific shortcut. Microsoft’s Text to Columns instructions
  3. In the conversion wizard, choose Delimited for values separated by a character. Choose the delimiter that matches your data, such as Comma or Tab. If fields line up by fixed character positions instead, choose Fixed width and set the breaks in the preview.
  4. Review the preview. Confirm that each sample row lands in the intended fields and that quoted text or blank fields are not being divided incorrectly. Microsoft’s wizard steps include choosing a delimiter, reviewing the preview, selecting a destination, and finishing the conversion. Wizard instructions
  5. Choose a destination cell if the wizard offers that setting, and select a blank starting cell with enough clear columns to its right. Finish the wizard, then check rows with the most fields as well as rows with missing values.

For example, if A2 contains Mina Chen,Seattle,WA, a comma delimiter produces three fields: Mina Chen, Seattle, and WA. If a row has an empty field between two commas, inspect the preview and result to ensure the blank stays in the correct position. Keep the original column until you have checked the output and decided whether the split should replace it.

Use TEXTSPLIT when you want formula results

In Microsoft 365 for Mac and Excel 2024 for Mac, TEXTSPLIT can split text into columns and spill the results into nearby cells. For example, if A2 contains Mina Chen,Seattle,WA, enter this in B2:

=TEXTSPLIT(A2,",")

Excel returns the pieces across the row. Fill the formula down to process more records. Because results spill into adjacent cells, clear the required output area first; occupied cells can prevent the results from appearing. The formula remains linked to the source, so changes to A2 can update the split output. Microsoft lists TEXTSPLIT as available in Microsoft 365 for Mac and Excel 2024 for Mac, and documents its delimiter arguments and spill behavior. TEXTSPLIT function documentation

Use the wizard for a one time conversion. Use TEXTSPLIT when you want the separated fields to update with source edits or need a formula you can copy down. TEXTSPLIT is unavailable in older Excel releases; if Excel returns #NAME?, check your version or use Text to Columns instead. For a space delimiter, remember that every matching space can create another field: names with middle names or locations with multiple words may not fit a simple two column split.

Check results and preserve important values

Compare several output rows with the source, especially entries containing extra delimiters, consecutive separators, blanks, dates, and numbers with leading zeros. Splitting text is separate from deciding how Excel should interpret each field. If a value such as 00417 is an identifier, check that it remains text rather than becoming 417; if a date or decimal changes, correct its format before relying on it.

If you are bringing data in from a CSV or text file rather than splitting cells already in a workbook, use Excel’s Mac import workflow: Data > Get & Transform Data > Get Data > From File, then choose From Text/CSV. Microsoft documents the import and worksheet destination choices for Excel for Microsoft 365 for Mac, Excel 2024 for Mac, and Excel 2021 for Mac. Import data from a CSV, HTML, or text file

When the split is correct, add or restore headers for the new fields and save the workbook in Excel format if you need to retain workbook features. A quick preview, clear destination, and spot check of irregular rows help prevent the most common errors: overwriting neighboring data or dividing a value at the wrong separator.

Sources