Excel users often have the need to split text in one column into one or more different columns. This tutorial provides step-by-step instructions for splitting textual data with Excel's Text to Columns feature.
I have over 500 MAC addresses and I'm trying to find a simple way to insert colons between every 2 characters. This video shows how to split a text into columns in word 2016. It also shows how to add a line between columns, and how to change columns width.If you like.
Why split data? Often there is a need to sort, filter, or perform mathematical calculations on data that is not currently in its own column. A classic example is having first and last names in a worksheet column and wanting them in separate columns.
Rules for Successfully Splitting Excel Column Data
A. The data to be split must all have a common delimiter—one or more characters that create a boundary. Delimiters can be spaces, commas, tabs, or any symbol or special character not found in the data itself.
B. Excel puts the split data in one or more columns to the right, so insert several empty columns before doing a split so Excel won't overwrite existing data.
C. Some cells may not split correctly. For example, if splitting names with a space as the delimiter, entries with a middle name, like Sue Ann Jones, will split into three columns instead of two, and will have to be corrected. See the last section for more information.
How to Split When Comma, Semicolon, Tab, or Space is the Delimiter
Follow these directions if the delimiter is a comma, tab, space, or semi-colon.
► 1. Make sure you have one or more empty columns to the right. Then select the cell whose contents you want to split. In our example we select Column A.
► 2. On the ribbon's Data tab, find the Data Tools section and click Text to Columns.
► 3. The Convert Text to Columns wizard opens. Select Delimited and click Next.
► 4. Now we specify which character Excel should use to split the data. Make sure a checkmark is in just one of the boxes under Delimiter—Comma in our example. A preview of the results displays at the bottom. Click Next.
► 5. The last window lets the user select each column's data format. If you don't want to use the default, highlight each column and make a selection. Then click Finish.
► 6. Excel returns to the worksheet with the first column highlighted. The split data in the new column will have the worksheet's default color and font.
How to Split When a Symbol or Special Character is the Delimiter
Data may also be separated by hyphens, dashes, slashes, pipes, or any symbol or unique character. If so, follow these steps. (And see the last section for potential problems if the character used as the delimiter is also in the data itself.)
► 1. Insert one or more empty columns to the right. Then select the cell whose contents you want to split. In our example, a hyphen is the delimiter.
► 2. On the ribbon's Data tab, find the Data Tools section and click Text to Columns.
► 3. The Convert Text to Columns wizard opens. Select Delimited and click Next.
C. Some cells may not split correctly. For example, if splitting names with a space as the delimiter, entries with a middle name, like Sue Ann Jones, will split into three columns instead of two, and will have to be corrected. See the last section for more information.
How to Split When Comma, Semicolon, Tab, or Space is the Delimiter
Follow these directions if the delimiter is a comma, tab, space, or semi-colon.
► 1. Make sure you have one or more empty columns to the right. Then select the cell whose contents you want to split. In our example we select Column A.
► 2. On the ribbon's Data tab, find the Data Tools section and click Text to Columns.
► 3. The Convert Text to Columns wizard opens. Select Delimited and click Next.
► 4. Now we specify which character Excel should use to split the data. Make sure a checkmark is in just one of the boxes under Delimiter—Comma in our example. A preview of the results displays at the bottom. Click Next.
► 5. The last window lets the user select each column's data format. If you don't want to use the default, highlight each column and make a selection. Then click Finish.
► 6. Excel returns to the worksheet with the first column highlighted. The split data in the new column will have the worksheet's default color and font.
How to Split When a Symbol or Special Character is the Delimiter
Data may also be separated by hyphens, dashes, slashes, pipes, or any symbol or unique character. If so, follow these steps. (And see the last section for potential problems if the character used as the delimiter is also in the data itself.)
► 1. Insert one or more empty columns to the right. Then select the cell whose contents you want to split. In our example, a hyphen is the delimiter.
► 2. On the ribbon's Data tab, find the Data Tools section and click Text to Columns.
► 3. The Convert Text to Columns wizard opens. Select Delimited and click Next.
► 4. Under Delimiters, ensure only Other is checked, and type the character you're using as the delimiter in the white box. A preview at the bottom shows the data when split. Click Next.
► 5. Modify the data format for each column if desired, or take Excel's default format. Then click Finish.
Split Text To Columns In Mac For Addresses Using
► 6. Excel returns to the worksheet with the data split into two columns and the first column highlighted.
Problem: Inaccurate Splitting of Data
If the delimiter appears elsewhere in the data, some results will be inaccurate. In this example, both a book title (Dog-Friendly Hotels) and an author's last name (Ron Smith-Davies) contain hyphens—our delimiter. Because Excel will split the data every time it sees a hyphen, rows 2 and 3 have extra columns as seen in the preview image below.
So add extra blank columns to the right before splitting the data, look at the preview, and correct any errors after the split.
Split Text To Columns In Mac For Addresses Free
Over-writing Existing Worksheet Data
When using Text to Columns, Excel uses as many columns as necessary to complete the split. In this example, the delimiter, a space, is repeated in the data, and we've only one blank column. So a column of data will be overwritten.
When Excel needs to overwrite existing data to complete a split, it displays this warning.
If we click OK, Excel completely overwrites the next column (or columns) regardless of how many rows actually need the extra cell. In our sample worksheet, Column F contained the Hire Dates, but now all of that data is gone!
Being able to split cell data into multiple cells is a great Excel feature. But inspect your data carefully, leave extra blank lines for potential issues, look at the preview before finishing, and correct any problems after splitting the data. We hope this article on splitting data from one column into multiple columns has been helpful. Cheers!
↑ Return to the top
