Excel treats numbers as text

[Ann_Woo]

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

[#2 · Accepted solution]

@Ann_Woo 

In 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'.

[#3]

@Ann_Woo 

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) )
  )