cannonballdex wrote:I found the simplest solution to this somewhere and had to share it.
Linked data gives you ('$100.50) with leading apostrophe
=VALUE('linkeddata'.A3) gives you only the value.
The simplest solution is this formula applied to thousands of wrongly imported csv values?
Now do the same with 100,50€ which may occur in any csv file from the Euro zone.
Calc can handle any of these (even in the same file) if you do one or two more clicks.
When I import US data, I choose "English (USA)" as import language.
When I import German data, I choose "German (Germany)" as import language.
In the very rare case where 100,50€ and $100.50 occur in different columns of the same file, I choose German import language and mark the dollar column as "English (US)"
"Detect special numbers" should always be checked. You may turn it off for a few special cases. Whenever there is an apostrophe in front of a numeric expression, this tells us that this would be a number if it had not deliberately imported as a text value. "Deliberately imported as a text value" means that you did not check the option which lets the program interprete special numbers such as decimals with currency symbols. You did not check that option, so you do not get special numbers interpreted. SImple as that.