Generally, Excel can detect tab-separated text and paste it into a different column automatically. However, if this automatic detection doesn't work and all of the text you copied is pasted into one column, you may need to check your Excel settings or the text you pasted. Excel might be set to accept a different separator character, or the text you paste might be separated by spaces instead of tabs. The Text to Columns function in Excel can select the right separator character, and separate the data into different columns properly.
Step
Step 1. Copy the entire tab separated text
Tab-delimited text is a format for storing tables in the form of text files. Each cell is separated by tabs, and each entry is in its own row. Select the text you want to copy into Excel, then copy the text.
Step 2. In Excel, select the destination cell
The data you copy will be pasted into the cell you selected and the cells below and to the right of it.
Step 3. Paste the data you just copied
If you are using a new version of Excel, and if your data has been tab-separated, the data will be pasted in the appropriate cells. The tab on the data will be the new cell marker. If all of your data appears in one column, check your Excel settings to make sure you accept the type of separator character, such as a comma. You can change the accepted separator character via the Text to Columns function.
Step 4. Select the column containing the data
If your data is not pasted correctly, you can use Excel's built-in Text to Columns function to format it.
- Click a column letter at the top of the window to quickly select an entire column.
- The Text to Columns function can only handle data in one column at a time.
Step 5. Click the Data tab, then select Text to Columns
This option is in the Data Tools section.
If you are using Office 2003, click the Data menu > Text to Columns
Step 6. Select Delimited, then click Next
Excel will look for a specific character as a data separator.
Step 7. Select the data separator character
If your data is tab-separated, check the Tab option, and uncheck the other options. Check the data you pasted for the separator character. If your data is separated by multiple spaces instead of tabs, check the Space option, then select Treat consecutive delimiters as one. However, if your data has multiple spaces separated by spaces and is actually in the same column, the conversion process may be problematic.
Step 8. Select the first column format
After selecting the separator character, you can set the data format in each column. You can choose between General, Text, or Date.
- Select the General option if the data you paste is numbers and letters.
- Select the Text option if the data you are pasting is only text, such as a name.
- Select the Date option if the data you are pasting is a standard date format.
Step 9. Repeat the conversion process for the other columns by selecting the desired column and selecting the format
You can also ignore certain columns when converting data.
Step 10. Complete the process
After you have formatted each column, click Finish to apply the changes. Your data will be separated according to the settings you selected in the Text to Column window.
