How to Copy Paste Tab Delimited Text Into Excel

PDF download Download Article
PDF download Download Article

Excel can typically automatically detect text that is separated by tabs (tab-delimited) and properly paste the data into separate columns. If this doesn't work, and everything you paste appears in a single column, then Excel's delimiter is set to another character, or your text is using spaces instead of tabs. The Text to Columns tool in Excel can quickly select the proper delimiter and divide the data into columns correctly.

Pasting with Delimiter Excel

  1. Select the cells in Excel you want your data to appear in.
  2. Click the "Paste" button in the menu at the top.
  3. Select the column of data, open the "Data" tab, and click "Text to Columns."
  4. Click "Delimited" and click "Next."
  5. Check the "Tab" box if your data is tab-limited.
  6. Repeat for the other columns, then click "Finish."

Steps

PDF download Download Article
  1. Tab-delimited text is a format for storing data from a spreadsheet as a text file. Each cell is separated by a tab stop, and each record exists on a separate line in the text file. Select all of the text you want to copy to Excel and copy it to your clipboard. [1]
  2. Select the upper-leftmost cell that you want your pasted data to appear in. Your pasted data will fill up the cells below and to the right of your starting cell.
    Advertisement
  3. In newer versions of Excel, and if your data was properly delimited with tab stops, the cells should fill out appropriately with the correct data. Each tab stop should translate directly into a new cell for the data. [2] If all of your data appears in a single column, there's a good chance Excel's delimiter was changed from tabs to something else, such as a comma. You can change this back to tabs by using the Text to Columns Tool.
  4. If your tab-delimited data did not paste correctly, you can use Excel's Text to Columns tool to format it properly. To do this, you'll need to select the entire column that contains all of the data you pasted.
    • You can quickly select the entire column by clicking the letter at the top.
    • You can only use Text to Columns on a single column at a time.
  5. You'll find this in the Data Tools group in the Data tab. [3]
    • If you're using Office 2003, click the Data menu and select "Text to Columns".
  6. This will tell Excel that it will be looking for a specific character to mark cell divisions. [4]
  7. If your data is tab-delimited, check the "Tab" box and uncheck any other boxes. You can check different characters if your data was separated by something else. If your data was split by multiple spaces instead of a tab stop, check the "Space" box and the "Treat consecutive delimiters as one" box. Note that this may cause problems if you have spaces in your data that don't indicate a column division. [5]
  8. After selecting your delimiter, you'll be able to set the data format for each of the columns that are being created. You can select between "General", "Text", and "Date". [6]
    • Choose "General" for numbers or a mix of numbers and letters.
    • Choose "Text" for data that is just text, such as names.
    • Choose "Date" for data that is written in a standard date format.
  9. Select each column in the frame at the bottom of the window and choose the format. You can also choose not to include that column when converting the text. [7]
  10. Once you have formatted each of the columns, click Finish to apply the new delimiter. Your data will be split into columns according to your Text to Column settings. [8]
    11. Advertisement

Community Q&A

Search
Add New Question
  • Question
    How do I copy and paste in to an excel cell mid sentence?
    Community Answer
    To copy and paste into an excel cell mid sentence, you'll have to go look at the formula bar and select the part of the text that you'd like. From the formula bar, you don't have to select the entire cell. Then, copy normally from that selection (Control+C on Windows), and then go to the desired place and paste it. If you wish to paste into a cell mid sentence, again, go up to the formula bar and select where you want to paste it.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 2 Helpful 4
  • Question
    What do I do if a 13 digit number in the first column is coming up short with E+13?
    Community Answer
    Right-click on the cell with the 13 digit numbers. Select "Format "Cells in the drop-down menu. on the "Number" tab, under Category, select "Custom". Under "Type", select "0". Click "OK". Make sure your cell width is wide enough to show the numbers completely.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 1 Helpful 1
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      How to
      Make a Spreadsheet in Excel
      New to Excel? Here's Super Easy Tricks to Get You Started
      How to
      Link Sheets in Excel
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/copy-and-paste-using-the-office-clipboard-714a72af-1ad4-450f-8708-c2931e73ec8a
      2. ↑ https://support.microsoft.com/en-us/office/move-or-copy-cells-and-cell-contents-803d65eb-6a3e-4534-8c6f-ff12d1c4139e
      3. ↑ https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7
      4. ↑ https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7
      5. ↑ https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7
      6. ↑ https://newsroom.unl.edu/announce/snr/4298/24552
      7. ↑ https://newsroom.unl.edu/announce/snr/4298/24552
      8. ↑ https://newsroom.unl.edu/announce/snr/4298/24552

      About This Article

      Article Summary X

      1. Copy the text.
      2. Select a cell.
      3. Click the Paste menu.
      4. Select the entire column of data.
      5. Click the Data tab.
      6. Click Text to Columns .
      7. Select Delimited and click Next .
      8. Select Tab and click Next .
      9. Select formatting options.
      10. Click Finish .

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 510,319 times.

      Reader Success Stories

      • Juan Lyon

        May 25, 2017

        "Awesome! wikiHow is a brilliant site. It makes it so easy to learn!"
      Share your story

      Is this article up to date?

      Advertisement