how to move decimal and comma points in excel?

Posted on

QUESTION :

i have a .txt file, where i have numbers such as-

99.69599  
100.7113  
101.7196
123.93

but whenever i am exporting them to excel, they become like this-

9.969.599
1.007.113
1.017.196
123.93

please note that the numbers in the .txt file in the US English format but the excel is in German format. so in the excel (German format) decimal point and comma are opposite meaning it uses “.” to mean “,” and “,” to mean “.”.

  • US / English thousands and decimal separators: 1,234.56
  • European thousands and decimal separators: 1.234,56

and the main problem is excel is for some reason shifting the decimal point when the numbers are being exported. can anyone please help me with this problem?

ANSWER :

Two things since the technique to do the work is basically already answered:

1) The issue arises because Excel is interpreting the text as if formatted in the German manner, so the periods, which are decimal separators in English usage, are seen by YOUR Excel as misplaced integer groupers. For instance, it would think “99.34567” is what an English using Excel-er would see as “99,34567” and that some goof placed the comma. It then fixes that for you and “properly” places the separators.

You can fix it before importing, which is easily done with any simple program like NotePad or WordPad, and you’ll prefer to do so if you are importing the file by opening it with Excel.

If Excel is already open and you are importing into the already open Excel, you can simply format the column you will dump the data into as Text BEFORE dumping the data in, and then do the operations described in Excel. Whichever seems good to you.

2) DO NOT insert a single quote ( ‘ ) because in many, many cases Excel will NOT make that available to remove and you’ll have a couple extra steps to do to get clean numbers. Maybe not ALL cases, but so very, very many…

Re-reading the comments (at time of writing) it seems as the “sane” (regional agnostic!) way to get this done is

  • make sure the numbers appear as text intitially (after the import).
  • Then do replace the/any thousands separator with a null string (“”),
  • followed by a replace on the decimal separator to the one that Excel actually uses.
  • Then do an “Add zero” (or multiply by one) using Paste Special, making Excel’s text-data formatting change into “number”, after selecting all data.

The first step may require (need) some sed, awk, (insert favorite scripting gadget name here) or “python magic”, e.g. inserting a single quote ' before all numbers – which Excel interprets as a literal “This is Text” marker.

The last step is:
Enter a 1 in a random cell, Select the cell and Copy it, then select the data you wish to transform and do “Paste special”, select multiply in the dialogue, click OK.

Leave a Reply

Your email address will not be published. Required fields are marked *