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?
Google-sheets – How to copy formatted formula results in Google Sheets
google sheets
Related Topic
- Google-sheets – Universal formula for generating Google Spreadsheet hyperlinks from cell contents
- Google-sheets – How to show cell data as a value when referencing another cell that includes a formula
- Google-sheets – Google Sheets split function is converting text to date and then splitting
- Google-sheets – Google Sheets – copy and paste a cell’s value while preserving a hyperlink
- Google-sheets – Why is this Google Sheets formula counting blank cells
- Google-sheets – How to make a formula ignore a blank cell in Google sheets
- Google-sheets – Google sheet showing #VALUE! error for non-formula cell
Best Answer
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 A13Edit: 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
andA16
accordingly.A13
is the first cell,A16
is the last cell. You may also write it asA13:A
and that will take all the rows starting for A13 under consideration.