Excel - Do not want automatic Text to Columns

Posted on 2011-10-21
Last Modified: 2012-05-12

When I paste information from a text file into an Excel spreadsheet, the data gets automatically separated into columns.

Is there a way to prevent this? I have already tried to change the format of the worksheet.

Thank you.
Question by:tahirih
    LVL 3

    Expert Comment

    Can you provide an example file?  Is there a reason why you do not open the file directly from excel and use the conversion tool to map the columns if any needed?
    LVL 26

    Accepted Solution

    Hi, tahirih.

    Without seeing you text file, I suspect that it is using tabs. When you paste the data into Excel, it uses the Text to Column delimiter setting - which defaults to the tab. You could bypass this by changing the setting to have no delimiter and so Excel would treat the tab as just another character.

    There are a couple of problems with this approach...
    (1) When Excel loads, it resets the delimiter default back to tab.
    (2) Excel doesn't handle the tab character in a cell very well. There's no visible sign that it's there - until you edit it, when peculiar things happen.

    Echoing ajcheung78's request, could you post an example of both the source test file (just a few entries and changed as necessary for confidentiality) plus the desired Excel results?



    Author Closing Comment

    Thank you.
    LVL 26

    Expert Comment

    Thanks, tahirih.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now