Forum Discussion
Excel Clipboard Format
If this is the wrong place for this question, please point me to the correct place.
If you copy data from Excel, and then paste it into another application, you will generally get numbers formatted in the same manner they appeared in Excel. For for example, if you have a cell with the value "1.025368" that is formatted to 3 decimal places (1.025), and you copy and paste this to another application, then "1.025" is what will be pasted.
If you are writing a program that wants to read the full number from the system Clipboard, there are multiple formats that the clipboard can hold. The normal format is "Text" -- where each cell is separated by a tab, and each row by a newline character. But when you copy from Excel, it also inserts an "XML Spreadsheet" format of data that looks like this when you retrieve it:
<ss:Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<ss:Styles>
<ss:Style ss:ID="Default" ss:Name="Normal">
<ss:Alignment ss:Vertical="Bottom" />
<ss:Borders />
<ss:Font ss:FontName="Arial" x:Family="Swiss" ss:Color="#000000" />
<ss:Interior />
<ss:NumberFormat />
<ss:Protection />
</ss:Style>
<ss:Style ss:ID="s63">
<ss:NumberFormat ss:Format="0.0000" />
</ss:Style>
<ss:Style ss:ID="s64">
<ss:NumberFormat ss:Format="0.000" />
</ss:Style>
</ss:Styles>
<ss:Worksheet ss:Name="Sheet1">
<ss:Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="5">
<ss:Row>
<ss:Cell ss:Index="2" ss:StyleID="s64">
<ss:Data ss:Type="Number">1.0253680000000001</ss:Data>
</ss:Cell>
</ss:Row>
<ss:Row ss:Index="4">
<ss:Cell ss:Index="3" ss:StyleID="s63">
<ss:Data ss:Type="Number">15.236548900000001</ss:Data>
</ss:Cell>
</ss:Row>
</ss:Table>
</ss:Worksheet>
</ss:Workbook>
So far so good...this allows us to pull out the full number. However, the <ss:Row> and <ss:Cell> values are only present for non-blank rows and non-blank cells. You can see that they have an "Index=" attribute that is supposed to tell you what row number and column number these values belong in.
The problem is that this doesn't seem to be true for the very first row. In the above code, I had actually copied a blank row, as shown in this next picture:
There doesn't seem to be a way to know that the first cell value is actually on the second row instead of the first. This seems like a bug with Excel, right? How does one get that addressed?
1 Reply
1. The use of “selective paste” function: copy the content, right-click the target cell, select “selective paste”.
Select paste options (“value”, “format”, “formula”, etc.) as needed.
2. Check the clipboard settings:
Open Excel, click File > Options > Advanced.
In the Clipboard section, make sure the Show Paste Options Button is enabled.
3. Use the Formatting Brush: Select the source cell and click “Start” > “Formatting Brush”.
Click on the target cell to apply the formatting.
4. Clear the target cell formatting: Select the target cell and click Start > Clear > Clear Format.
Repaste the contents.
5. Check cell formatting: Select the target cell, right-click and select “Set Cell Format”.
Make sure the formatting is consistent with the source cell.
6. Use VBA Scripts: If you need more complex formatting to copy, you can use VBA scripts:
vba
Sub CopyFormat()
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub
Add this script to Excel's VBA editor and run it to copy the formatting.
7. Update Excel: Open Excel, click File > Accounts > Update Options > Update Now “.
Make sure Excel is updated to the latest version.
8. Contact Microsoft: If the issue remains unresolved, submit the issue through Microsoft Support.
Provide detailed error messages and screenshots to quickly diagnose the issue.
