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