When pasting values from another sheet, How to bulk fix invalid data errors?

I pasted data from a google sheet. The data pasted as text even though it was numbers. When I changed the columns type to numerical it throws an invalid error.

I can of course change each cell manually, but is there an easier way to do this using a formula or some other method?

This issue with pasting formatted numbers is definitely something we need to fix. It’s already on our to-do list.

Meanwhile, there is a workaround.

In the column with dollar amounts, open the column settings, and look for “Optional formula”. There is a way to set a formula for data cleaning, which we can use to essentially strip out the formatting from numbers (like “$” or thousands separators).

Into “Optional formula”, enter this data-cleaning formula:

if not ISTEXT(value):
  return value
IFERROR(float(str(value).replace("$", '').replace(",", "")), value)

Then check the box “Apply on changes to:” and in the dropdown select “Current field” and click “OK”. As in this screenshot:

Now, pasting formatted values into this column will convert them properly to numbers.

To fix the pink values already there, select them, hit Ctrl-C to copy, then hit Delete or Backspace to clear those cells, then Ctrl-V to paste back in. On pasting, the new data-cleaning formula will apply and the numbers should paste in correctly.

1 Like