How do I copy formatted formula results in Google Sheets?

Question

I have formula cells outputting numerical results that are then formatted with a " km" (kilometres) post-fix. For example, the formula returns 21.4, which is then formatted as 21,4 km. I want to copy and paste the numerical value, so I copy and then on the target cell select "Paste values only". But this assigns the cell with a text value of "21,4 km", and I need the numerical value 21.4 instead. How do I do that?

Answer 1

Alternate way to do this. Consider this sheet below.

=ARRAYFORMULA(REGEXEXTRACT(A13:A16,"(\d+.\d+)"))

For a series of numbers we can do this.

For a single cell, use =REGEXEXTRACT(A13, "(\d+.\d+)") assuming the value is in A13

Edit: As suggested by Oleg S, the final step is to use the VALUE() function to get the numerical value. So far it's just text.

Revised formula: =VALUE(REGEXEXTRACT(A13,"\d+.\d+") for a single cell.

And for the whole column =ARRAYFORMULA(VALUE(REGEXEXTRACT(A13:A16,"\d+.\d+")))

Replace the values A13 and A16 accordingly. A13 is the first cell, A16 is the last cell. You may also write it as A13:A and that will take all the rows starting for A13 under consideration.