2
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.
Which version of Excel? – Julian Knight – 2016-12-11T16:30:33.973