Forum Discussion

Ann_Woo's avatar
Ann_Woo
Copper Contributor
Jun 30, 2021
Solved

Excel treats numbers as text

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 y...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Jun 30, 2021

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

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 30, 2021

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

Resources