Jun 30 2021 12:04 AM
Hello Community,
I have downloaded an excel file from Visma (accounting software) in csv format.
Excel does not recognize the numbers as such and does not even show any error warning (normally you get the small green rectangle in the upper left corner of the cell).
The following trials all failed:
- use "paste special" with add or multiply
- use =value formula
- use "Data" - "Text to Columns" "Fixed with" "Finish"
- change the format from "general" to number
- converted csv to xlsx via an online converter
Please find attached the file. Is there anybody who knows the redeeming solution?
Kind regards,
Ann
Jun 30 2021 12:34 AM
SolutionIn the first place, the values use comma as decimal separator.
If you use point as decimal separator, press Ctrl+H to activate the Replace dialog.
Enter a comma in the 'Find what' box and a point in the 'Replace with' box, then click 'Replace All'.
Of course, if you use comma as decimal separator, you should skip that.
In the second place, the values use a space as thousands separator.
Press Ctrl+H to activate the Replace dialog.
Enter a space in the 'Find what' box and clear the 'Replace with' box so that it is empty, then click 'Replace All'.
Jun 30 2021 05:53 AM
It is also possible to use PowerQuery to import the data, removing spaces and adjusting separators as required.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ReplacedCommas = Table.ReplaceValue(Source,",",".",Replacer.ReplaceText,{"Debet","Kredit","Saldo"}),
RemovedSpaces = Table.ReplaceValue(ReplacedCommas," ","",Replacer.ReplaceText,{"Debet", "Kredit", "Saldo"}),
#"Changed Type" = Table.TransformColumnTypes(RemovedSpaces,{{"Debet", type number}, {"Kredit", type number}, {"Saldo", type number}})
in
#"Changed Type"
This is best if you can import the data from source using PowerQuery.
An alternative for Excel 365 users with the authority to select insider beta is to use a Lambda function to hide the messy formulas.
= LAMBDA(a,
LET(
t, SUBSTITUTE(B5:B13," ",""),
v, SUBSTITUTE(t, ",","."),
IFERROR(--v,t) )
)
Jun 30 2021 12:34 AM
SolutionIn the first place, the values use comma as decimal separator.
If you use point as decimal separator, press Ctrl+H to activate the Replace dialog.
Enter a comma in the 'Find what' box and a point in the 'Replace with' box, then click 'Replace All'.
Of course, if you use comma as decimal separator, you should skip that.
In the second place, the values use a space as thousands separator.
Press Ctrl+H to activate the Replace dialog.
Enter a space in the 'Find what' box and clear the 'Replace with' box so that it is empty, then click 'Replace All'.