6

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

3 Answers 3

3

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

3
  • Thanks. That didn't answer straight to my question but I edited the question to clarify myself a bit.
    – touko
    May 20, 2011 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. Sep 1, 2014 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). Sep 1, 2014 at 5:06
2

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.

0

I found this out just now when asking the same question!

You need to use the formula =TEXT(<val>,"#"), where <val> is the first argument that takes whatever the value is, and the second argument is how the text should be formatted. In my case it's all numbers so using "#" does it, so your mileage may vary. This is an old question, but I feel happy that I solved the issue, personally, so I wanted to share, in case someone else finds themselves in the same situation!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .