0

When importing an xlsx file into a Google sheet, I end up with a formula parse error because the cells of one column contain wrong date formulae like ==DATE(2021,3,12). Obviously the problem is that there is one "=" to many.

I cannot go ahead and manually fix the error in every cell, so I would need some other formula to get rid of the of the additional "=" at the beginning of the cell content. I thought about using something like =RIGHT(), but this again results in an error, since the referenced cell already contains an error and cannot be interpreted as string.

In the end I would like to import the column containing the (wrongly formatted) dates into another spreadsheet via the =IMPORTRANGE() formula and at the same time get rid of the additional "=" at the beginning of the cell content.

If possible I would like to do this without the help of a script, but if it's only possible with one, then I am grateful for ideas here as well. Any help is greatly appreciated!

Schnodahipfe
  • 21
  • 4
  • Would a search for `==`, replacing it with `=` work, maybe? – cybernetic.nomad Jan 12 '22 at 21:22
  • Thanks a lot, this is quite helpful! I was kind of hoping to find a less manual way so that I simply need to import the latest version of the xlsx and that the other Google sheet that I import the data to via IMPORTRANGE() automatically has the correct dates in there – Schnodahipfe Jan 13 '22 at 08:07

1 Answers1

1

It's doesn't make sense to use a formula to fix another formula as that it's very likely that will require too much effort compared with other options i.e. use find and replace to replace all ==DATE by =DATE, just check the checkbox to search inside formulas. This could be done before or after importing to Google Sheets as Excel and Google Sheets have similar features for this.

Related

Excel

Google Sheets

Rubén
  • 29,320
  • 9
  • 61
  • 145
  • Thanks a lot, this is quite helpful! I was kind of hoping to find a less manual way so that I simply need to import the latest version of the xlsx and that the other Google sheet that I import the data to via IMPORTRANGE() automatically has the correct dates in there – Schnodahipfe Jan 13 '22 at 08:07
  • @Schnodahipfe This could be done before or after importing the Excel file in Google Sheets. There might be other options but IMHO the search/replace is the simplest specially when you don't want to use script. – Rubén Jan 13 '22 at 15:40