5

I have one spreadsheet in Google Sheets with data like:

0012
0234
0065

I want to paste this in another Google Sheets but when I use ctrl+c ctrl+v the data is formatted like numbers:

12
234
65

I want conserve the string format.

With right-click copy-paste I can't copy between spreadsheets.

Tested in Firefox and Chromium.

1
  • 1
    Very strange... Edit>Copy and Edit>Paste do not actually copy/paste anything between spreadsheets! Yet the keyboard shortcuts Ctrl+C and Ctrl+V (which are allocated to these menu items) do copy the values, but lose all formatting! – MrWhite Aug 24 '12 at 10:15
4

Place a single quote (') in front of the zeros before you copy the cells.

If you need to apply this to lots of cells, set the contents of one cell to ' and use CONCAT to populate a new column with the ' and the cell contents.

If A1 is ' and the values you want to copy start in B1:

Set C1 to =CONCAT($A$1, $B1) and fill the formula down.

2
  • this doesn't work if the numbers have leading 0s due to the number format of the cells :/ – Salix Oct 7 '20 at 17:52
  • Well, maybe it did 8 years ago lol. Now the copy/paste works for that, but in case someone else stumble here because they wanted to concat the number with a string and still have the leading 0s, you can do '=TEXT($B1, "0000")'. – Salix Oct 7 '20 at 18:01
1

The only workaround I see: press ' before pasting. It will preserve string format.

Pasting in the same spreadsheet preserves formatting. Pasting into another, says Pasting from web clipboard...

1

I had a similar problem and found a good solution.

Copy the table to some simple text editor that supports regex. I used notepad++. Then insert the decimal separator before the numbers. Then you can copy the table to the spreadsheet.

E.g. if you have 3 digits without a decimal separator in front, you search replace using (\d\d\d) and .\1.

0

I found another method that worked. Just save the file to a CSV format. Close and reopen the file, but choose to format columns as text. I have done it with Libre Office.

-1
  1. copy the cells using Ctrl+C

  2. go to the target spreadsheet

  3. highlight the cells you want to paste them on

  4. go to FORMAT > NUMBERS > and click PLAIN TEXT at the bottom (this formats the cells to text fields before you paste)

  5. paste using Ctrl+V on the cells

2
  • 1
    This don't work, neither Firefox nor Chromium – richie-torres Oct 26 '12 at 14:10
  • If it didn't work before, it works now. I pasted a large sheet of data from LibreOffice Calc into a Google Sheet, then selected the entire column that contained the broken numeric strings, pasted again over the top of the existing data and the leading zeroes were back. – Walf Sep 7 '20 at 7:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.