Calc Match destination formatting on paste

I’m trying to copy a table from a document or website into a template that I have the columns formatted how I want them. When I paste into that template it forces the data to whatever format it wants. Is there an option like in excel that when you paste you can force it to use the original formatting?

Added base tag, as this is related to format of data from base into calc.

Right click on the destination and select Paste Special…, then make sure Formats is not selected.

If you are copying from something that is not a spreadsheet, first add a blank sheet to your spreadsheet document, paste the copied data into that sheet. Copy the cells in that spreadsheet and then paste special to your template. Then you can remove the extra sheet that was added.

It might be possible to write a macro to make this easier. First let me know if this works for you.

If I select Paste Special I just get a list of different types of options of where the data is coming from such as RTF, HTML, XML. No Formats option.

Edit: This leads right back to the original problem that is driving this. I’m using base to output a report. I tried it directly to a spreadsheet but had to stop using that since one of the columns of data usually is a string of 9 numbers that start with a leading zero. Continued below.

Even with Calc default opening with an all text column template, it drops that leading zero. So I changed the output to a document and it works great, except I need to copy and paste into Calc which then will drop the leading zero because Calc doesn’t seem to have the Match destination format option like Excel does on pasted data.

Is it only the loss of leading zero on one column from base to calc that made you try this method? It might be better to try and fix that, instead of the isuue with pasting data. Could you format that column in your template as text instead of general? Any formula that need to use that column as a number value would then use the VALUE function to treat that text as a number.

Just checked to make sure, but in base the report column and the column in the table are both set to text. It’s Calc that auto formats when base kicks it out to Calc.

I’m hoping @Ratslinger or someone with Base experience can provide a solution direct from Base into Calc.

In trying to catch up here, I believe I see the situation. Here is a maybe depending upon what you are doing. You are generating this report from either a table or query. If you had just the data from that table or query in Calc would you have what is needed? I say this because you can bring registered data sources into Calc and it retains the original format:

image description

The figures in Col B were originally defined as a text field and both a table or query retains the leading zero. The cell format was not set prior but after data loaded it is Text.

I want to say thanks to both @Ratslinger and @mark_t for all the help. Using the registered data sources bypassed the whole problem.

Ctrl + V = Paste at source formatting
Crtl + Alt + Shift + V = Paste unformatted Text (matches the destination format)

Also available from the Edit Menu and rightclick menu.