formattinggoogle sheets
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.
If your text is in cell A1, then
=regexreplace(A1, "([0-9]+)", "($1)")
will do the job. The term you were looking for is capturing: the parentheses around [0-9]+ capture the matched substring, i.e., it gets recorded somewhere. In the substitution string, the commands $1, $2, ... can be used to refer to the 1st, 2nd, etc captured string.
So, the command says: find a string of consecutive digits (greedily, i.e., grab all consecutive ones), and replace it by the same string surrounded by parentheses. And being a global search-and-replace, regexreplace does the above to every matched substring.
It appears that the hours' field is required for time to be recognized as such. Enter 0:mm:ss.SSS (assuming decimal dot; could be a comma in your locale), for example 0:22:22.222
The default format for duration does not include the milliseconds' field, eg. use a custom one:
The first field should be "elapsed" to make the format a duration format: e.g., 70 minutes will stay 70 minutes without carrying over to hours.
Best Answer
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 useCONCATto 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.