We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Calc Match destination formatting on paste

asked 2016-08-26 14:28:42 +0200

Hisha gravatar image

updated 2020-09-21 23:18:50 +0200

Alex Kemp gravatar image

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?

edit retag flag offensive close merge delete


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

mark_t gravatar imagemark_t ( 2016-08-26 20:09:27 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2016-08-27 00:35:55 +0200

Ratslinger gravatar image

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.

edit flag offensive delete link more


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

Hisha gravatar imageHisha ( 2016-08-29 15:37:47 +0200 )edit

answered 2016-08-26 15:19:48 +0200

mark_t gravatar image

updated 2016-08-26 18:46:35 +0200

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

Edit: 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.

edit flag offensive delete link more


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.

Hisha gravatar imageHisha ( 2016-08-26 17:29:13 +0200 )edit

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.

Hisha gravatar imageHisha ( 2016-08-26 19:33:36 +0200 )edit

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.

mark_t gravatar imagemark_t ( 2016-08-26 20:01:40 +0200 )edit

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.

Hisha gravatar imageHisha ( 2016-08-26 20:28:54 +0200 )edit

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

mark_t gravatar imagemark_t ( 2016-08-26 23:46:08 +0200 )edit

answered 2019-11-29 07:11:44 +0200

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-08-26 14:28:42 +0200

Seen: 1,277 times

Last updated: Nov 29 '19