2

I have a .NET Windows Forms applications and I need to copy a list of 8-digit numeric codes into the clipboard to be pasted to Excel sheet.

string tabbedText = string.Join("\n", codesArray);    
Clipboard.SetText(tabbedText);

The problem is that when a code begins with one or more zeros (ex. "00001234") it's pasted as number with the zeros trimmed.

Is there a way how to set clipboard text so that Excel accepts it as text?

inative
  • 23
  • 5
  • IMHO, this question could get more answers on Superuser, since it's more like Excel & Clipboard. Unless you're looking for a way to change how `Copy` command behaves. – Tschareck Jun 07 '12 at 09:10

2 Answers2

3

I would treat this problem inside of Excel (and not in your application programaticaly). Format your cells to be treated as text, and then paste from clipboard. This way leading zeros are always pasted.

Tschareck
  • 4,071
  • 9
  • 47
  • 74
1

EDIT: This doesn't work in Excel, in that the apostrophe gets pasted in and shows up too. I'm leaving the answer here as an explicit statement that this approach won't help for Excel. It does work for OpenOffice Calc though.

The standard way to 'tell' Excel to treat a string as a string is to prefix it with an apostrophe. Have you tried something like:

string tabbedText = "'" + string.Join("\n'", codesArray);

(note the extra apostrophe in there... it's a bit hard to see).

Of course, this may cause you issues if you're planning to use this value thereafter in Excel calculations but there are ways to handle that too.

Holf
  • 5,605
  • 3
  • 42
  • 63
  • That would work only when you type in Excel with apostrophe. Then the number is not formatted, but if you paste, the apostrophe gets pasted too. – Tschareck Jun 07 '12 at 09:18
  • @TscharecK: Tested it in OpenOffice Calc and the apostrophe didn't get pasted. Only problem is that there need to be an apostrophe added at the beginning also. – Răzvan Flavius Panda Jun 07 '12 at 09:23
  • Adding the apostrophe was my first guess as well, but I'm afraid it doesn't work with excel. At least not in Excel 2010 where I tested it. – Me.Name Jun 07 '12 at 09:34
  • Yes, both the OP and Tschareck are correct. This doesn't work. I think Tschareck's answer is the way forward. – Holf Jun 07 '12 at 09:36