Paste numbers as string

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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