Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

when preparing table data as text to be copied to Excel through clipboard, is it possible to have certain cells unformatted? (If possible, without changing Excel settings or doing a special paste).

In a simple standard case I have one row of "column headers" (text) and rest of the rows contain numeric data

foo bar asdf
1   2   3

Now, the problem is that sometimes I got headers containing date stuff that Excel decides to format as date (or numbers sometimes). I'd like Excel to do no formatting for those

When entering the text to the cell by hand, I can do this by adding a single quote (') before the cell content. But if I copy the following to clipboard and paste it:

foo '1  asdf
1   2   3

then I get '1 to the screen. I'm not sure if there is some solution for this but if somebody has something, that would be nice =)

Why I'm asking this: My context is that I got a program that copies table data (as text) to clipboard (to be transformed to Excel).

EDIT: Some clarifications:

  • I'd like to do something to have the cells in the first row not being formatted
    • Clarified the actual question also
  • The optimum would be something such that the end user doing the paste wouldn't need to do any special paste
    • I'm not sure if this is possible, if that is the case I should find some way to export data in a more custom way but that is another story belonging to stackoverflow.com.

br, Touko

share|improve this question

2 Answers 2

After copying the table to clipboard you can use Text Import Wizard under Paste Menu in Excel 2007/2010. If you're using Office 2003 or earlier you can import table from an external text file, for details see this article.

At the 3rd step of Text Import Wizard you have the option to specify the column data format. Choose the column you want to change and select appropriate formatting.

enter image description here

share|improve this answer
    
Thanks. That didn't answer straight to my question but I edited the question to clarify myself a bit. –  touko May 20 '11 at 12:22
    
In my version, the Text Import Wizard was hiding under the Data tab item From Text (and wasn't under the Paste menu). This is Office 2007 on Windows XP. –  Evgeni Sergeev Sep 1 at 4:47
    
Actually, the "Text to Columns" function, on the same "Data" tab, worked better for me. Does a similar thing, but doesn't establish some kind of connection to the source file (which makes things inflexible). –  Evgeni Sergeev Sep 1 at 5:06

Set the number format for your header row to Text before pasting your data. Right-click the row > Format Cells > Number > Choose Text. Whatever value you paste into the cell will be converted to a string.

Now, if you ever need to use your header for calculations, use the VALUE formula.

Example:

  • If you paste 12/23/2011 in cell B1, it will show up as "12/23/2011" even if you change its number format afterwards.

    The formula VALUE(B1) will return the actual date value for 12/23/2011 (which is 40900 in decimal).

  • If you paste 234 in cell B1, it will show up as "234" even if you change its number format afterwards.

    The formula VALUE(B1) will return the decimal 234.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.