Paste to Excel from clipboard (plain text) : Can I make certain cells to be formatted "as such"

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.

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.