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?

1 Answer 1


Alternate way to do this. Consider this sheet below.



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
    Also add VALUE() to get numbers not text.
    – Oleg_S
    Aug 26, 2020 at 14:13
  • @OlegS Yes, thanks. May I add your comment in my answer? Aug 26, 2020 at 14:14
  • 1
    For sure add it.
    – Oleg_S
    Aug 26, 2020 at 14:21

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.