CODEWITHSUNDEEP
exceloffice365
Evgeniy
Evgeniy

Reputation: 2605

Convert scientific number format to number in german Excel

The source CSV contains numbers formatted like 13.558010465075. I want to import this CSV into german Excel without manual action of replacing point with comma. Without replacing these numbers are after import displayed in scientific notation like 1,3558E+13.

There are two methods to convert scientific number notations like 1,3558E+13 to number 13.558010465075:

  1. to use =trim()
  2. to use an option "format cells", like on the screenshot:

enter image description here

Both options don't work for me, maybe because I'm on german Excel. Don't work means: on usage of both methods decimal delimiter is lost, I get 13558010465075 instead of 13.558010465075.

Does somebody know any workaround for this issue?

Here are some first lines of my CSV data:

+----------------------+--------+---------+---------------------+
| URL                  | Top-10 | Top-100 | Anteil Sichtbarkeit |
+----------------------+--------+---------+---------------------+
| https://example.com/ | 232    | 1.259   | 13.558010465075     |
+----------------------+--------+---------+---------------------+
| https://example.com/ | 272    | 1.265   | 7.3158706637119     |
+----------------------+--------+---------+---------------------+
| https://example.com/ | 280    | 1.327   | 6.8266713736524     |
+----------------------+--------+---------+---------------------+

URL;Top-10;Top-100;"Anteil Sichtbarkeit"
https://example.com/;232;1.259;13.558010465075
https://example.com/;272;1.265;7.3158706637119
https://example.com/;280;1.327;6.8266713736524

Upvotes: 0

Views: 512

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

If I understand what you are writing correctly, the problem is that in the penultimate column, the dot represents a thousands delimiter, and in the last column, the dot represents a decimal delimiter.

This is pretty strange in a CSV file and I would double check with the source to get that corrected.

If that cannot be corrected, then Power query can be useful as you can separately format the two columns upon import, but you will have to get into the Advanced Editor to do so.

  • Import the file with GetData=>from Text/CSV=>Transform
  • Home=>Advanced Editor
  • Replace the automatically generated #"Changed Type" line as seen in the M-Code below:

M Code

let
    Source = Csv.Document(File.Contents("C:\Users\ron\Desktop\decimals.txt"),[Delimiter=";", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"URL", type text}, 
        {"Top-10", Int64.Type}, 
        {"Top-100", type any},  //<==
        {"Anteil Sichtbarkeit", type number}},
        "en-US" //<==
        ),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Top-100", type number}}, "de-DE")
in
    #"Changed Type with Locale"

For the first Change type we specify US settings (dot=decimal) and we specify the penultimate column as type any

Then, in a second step, we specify the penultimate column as being of German settings which will recognize the dot=thousands.

If we then close and load, we get the results below (as seen with German regional settings):

enter image description here

Upvotes: 2

Related Questions