Microsoft Excel: Text to Columns

Microsoft Excel: Text to Columns

Microsoft Excel: Text to Columns


Data imported from other spreadsheets or databases is already separated into fields, using something called a field delimiter: a comma, tab, space, or custom character – to separate one field from another. These databases import easily into Excel and place all the fields into separate columns. CSV (comma-separated values) is the most common data exchange format and, if offered, the best one to use. However, if you copy a block of data from a webpage, a Word document, or another text file, and then paste it into Excel, all the data is dumped into a single cell or a single column of cells, and the data is not delineated across columns.

Text to Columns: Delimited

When imported, it looks like the data spills over into the adjacent columns, but only the first column contains the pasted information.

1. Select the Column or Range of Data you wish to work with.

2. Expand the column to fit all of the text. This isn’t a requirement, but it helps to see everything more clearly. Do this by taking your cursor to the right of the column you wish to expand so you see the cross hairs, and double click. This will expand the column to fit the total length of the text.




Note: You can click the Arrow between Row 1 and Column A and it will select all columns across the worksheet, and you can do the cross-hair double-click on any column, and it will auto-adjust to fit text on all columns.




3. Select the Data ribbon tab and click on Text to Columns found in the Sort & Filter group.




4. In the Convert Text to Columns Wizard dialogue box (Step 1 of 3 below), choose the Delimited button (not fixed width), then click Next.




5. In the next dialog window (Step 2 of 3, below) select the delimiter—that is, the character that separates the fields. In CSV files, it’s a comma. Some databases use a tab, some use a semicolon. The only character that separates the data in this database is a space, so check the Space box.

6. Check the box that says: Treat consecutive delimiters as one, in case multiple spaces occur in one string of text. This setting also applies if your data has a delimiter of more than one character between data fields, or if the data contains multiple custom delimiters.

7. From the Text qualifier dropdown list, select None, because the records are not enclosed with single or double quotes.

8. Click Next. *You can click Finish at this point, but you will lose out on some additional options.




OPTIONAL: Setting Column Formatting

9. In the next dialog window (Step 2 of 3, below) select the delimiter—that is, the character that separates the fields. In CSV files, it’s a comma. Some databases use a tab, some use a semicolon. The only character that separates the data in this database is a space, so check the Space box.

10. Check the box that says: Treat consecutive delimiters as one, in case multiple spaces occur in one string of text. This setting also applies if your data has a delimiter of more than one character between data fields, or if the data contains multiple custom delimiters.

11. From the Text qualifier dropdown list, select None, because the records are not enclosed with single or double quotes.

12. Click Next. *You can click Finish at this point, but you will lose out on some additional options.




OPTIONAL: Splitting columns that contain surrounding data

13. Now say you want the months separated. First, insert a column beside the Modern Months column so the parsed data doesn’t overwrite the information in the column next to it (column G in our example). Do this by selecting the column you want a new column to the right of, right click on the letter, and select insert. This will insert a new blank column.

14. Highlight the column you wish to parse the data out of (in our example; the second field “Modern Months” in column F), and follow the instructions above to separate this one field into two fields. Note that the custom delimiter is the forward slash key.

15. Change the columns from General to Text and click Finish. Now the months are in two columns instead of one.


Text to Columns: Fixed-Width

When you’re splitting text to columns, Excel needs to know where to make the splits.

With Fixed-Width Splitting, Excel splits the text at specific character counts. So it might split the text at the 5th, 15th, and 30th character to the right. Say you want to split it out for a Year, you would split it at 4 characters.

Like the instructions above, select the column or text range and click on the Data tab, and select Text to Columns. This time select Fixed width.




Excel will do its best to figure out where you want to split the data. You can make changes to the line breaks, as follows:

1. Click on the preview to add a line (Excel will split your text at each line).

2. Double-click on a line to remove it.

3. Click and drag a line to move it.

4. Click Next. Now you can choose how to import each new column:




This is where you will get the opportunity to define the Column style; General, Text, Date, or Do not import column (skip).

You also need to set a destination for your new data. If you set the destination to the column that your data is coming from (as is the default), you will overwrite your original data. You can select a different column to deliver your data.




5. Click Finish to split your text into columns.

    • Related Articles

    • Microsoft Excel: How to select rows and columns

      Microsoft Excel: How to select rows and columns In this article, we will guide you through various methods to select rows and columns in Excel, including some helpful shortcuts. Efficiency is the name of the game when it comes to Excel. Selecting ...

    • Microsoft Excel: Concatenation (Concat)

      Microsoft Excel: Concatenation (Concat) Concatenation – What is it? The CONCAT function is one of Excel’s text functions. It is used to join two or more columns of text from multiple ranges and/or strings, but it doesn’t provide delimiter or ...

    • Microsoft Excel: How to select alternate columns

      Microsoft Excel: How to select alternate columns This article will guide you step-by-step through the process of selecting specific columns in Excel, such as every other or every nth column. Microsoft Excel, the go-to spreadsheet software for data ...

    • Microsoft Excel: How to freeze Rows & Columns

      Microsoft Excel: How to freeze Rows & Columns Freeze the Top Row or First Column Click on the View tab on the Excel Ribbon. Click the drop-down for Freeze Panes. Click Freeze Top Row – allows scrolling down many rows and still shows the top row. OR ...

    • Microsoft Excel: How to Add a Watermark to a Worksheet

      Microsoft Excel: How to Add a Watermark to a Worksheet Do you still think that you can’t add a watermark to your Excel worksheet? I have to say that you are all abroad. You can mimic watermarks in Excel 2019, 2016, and 2013 using the HEADER & FOOTER ...