Buzz
Typically, Excel can automatically detect text separated by tabs (also known as tab-delimited) and paste the data into separate columns correctly. If this doesn't happen and everything you paste appears in a single column, it means Excel's delimiter is set to something else, or your text is using spaces instead of tabs. Excel's Text to Columns tool can quickly help you choose the correct delimiter and paste the data into the appropriate columns.
Steps
all your tab-delimited text. Tab-delimited text is a format that stores data from cells into a text file. Each cell is separated by a tab, and each record appears on a separate line in the text file. Select all the text you want to copy in Excel and copy it to the clipboard.
Select the cell in Excel where you want to paste the data. Choose the top-left cell where you want your data to be pasted. Your data will fill the cells below and to the right of the selected cell.
Paste the data. In newer versions of Excel, if your data is properly tab-delimited, the cells will usually be filled with the correct data. Each tab-separated section will directly translate into a new data cell. If all your data appears in a single column, it’s likely that Excel’s delimiter has been changed from a tab to something else, such as a comma. You can switch it back to a tab using the Text to Columns tool.
Select the entire data column. If your tab-delimited data isn’t pasted as expected, you can use Excel’s Text to Columns tool to reformat it correctly. To do this, select the entire column containing all the pasted data.
- You can quickly select the entire column by clicking the letter at the top.
- You can only use the Text to Columns tool on one column at a time.
Click the Data tab and then click "Text to Columns." You’ll find this feature in the Data Tools section of the Data tab.
- If you’re using Office 2003, click the Data tab and select "Text to Columns."
Select "Delimited" and click "Next." This tells Excel to look for a specific character to separate the cells.
Choose the data delimiter. If your data is tab-delimited, check the "Tab" box and uncheck all others. You can select multiple delimiters if your data is separated differently. If your data is separated by multiple spaces instead of tabs, check the "Space" box and "Treat consecutive delimiters as one." Note that this might cause issues if there are unintended spaces in your data.
Select the format for the first column. After choosing the delimiter, you can set the data format for each column being created. You can choose between "General", "Text", and "Date".
- Use "General" for numbers or a mix of numbers and text.
- Choose "Text" for data that is purely textual, such as names.
- Select "Date" for data written in standard date formats.
Repeat the steps for other columns. Select each column in the preview window at the bottom and set its format. You can also choose to exclude a column from being converted into text.
Complete the delimiter process. After formatting all columns, click Finish to apply the new delimiter. The data will be separated into columns according to your Text to Columns settings.
