6

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, 2012 at 10:15

5 Answers 5

5

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, 2020 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, 2020 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 Oct 26, 2012 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, 2020 at 7:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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