I've got a bunch of CSV files that contain values with trailing units. This makes Excel think it's text so I can't sum them in a pivot table.
The data looks something like this:
Date; Text; Amount; 2016-12-11; Gas Station; -152,00 kr 2016-12-10; Food Stare; -130,00 kr 2016-12-09; Payment; 500,00 kr
I've managed to "solve" it by inserting a new column where I strip the unit and convert the text to a number using
=NUMBERVALUE(SUBSTITUTE(C2; " kr"; "")) and then telling Excel how to format that column and the value in the pivot table. But this feels really hackish.
Is there any way I can import the data and tell Excel that it already has the correct unit so I can use the data from the CSV directly?
Removing the units from the CSV files is not an option and I'd rather stay away from VB unless it's absolutely necessary.