Excel: turning comma into decimal point 20,00 -> 20.00
Thread poster: Anne Koth

Anne Koth  Identity Verified
Germany
Local time: 07:38
German to English
Jul 11, 2005

I'm sure this must be very simple, but how do I change the comma in my German Excel 97 into a decimal point but so that it still adds up the numbers in the columns?

[Edited at 2005-07-11 14:41]


Direct link Reply with quote
 

Carlos Montilla
Local time: 07:38
English to Spanish
+ ...
Local settings Jul 11, 2005

Go to:

control panel

local settings

at the local options tab, click on "customize" and change the "," with a ".".

Names may differ, as I'm translating from Spanish.

Hope it helps...


Direct link Reply with quote
 

Can Altinbay  Identity Verified
Local time: 01:38
Japanese to English
+ ...
I'm confused Jul 11, 2005

Anne Koth wrote:

I'm sure this must be very simple, but how do I change the comma in my German Excel 97 into a decimal point but so that it still adds up the numbers in the columns?

[Edited at 2005-07-11 14:41]


I thought this was a matter of formatting, so that as long as Excel recognizes that these are numbers, it will add them up into a cell with a formula. Are you saying that is not working?

Other than that, Carlos's answer should do it for you.


Direct link Reply with quote
 

Anne Koth  Identity Verified
Germany
Local time: 07:38
German to English
TOPIC STARTER
me too! Jul 11, 2005

I don't know Excel at all yet, so am rather confused too...

I tried changing the comma into a decimal point manually, but i guess it doesn't recognise them as numbers, as the comma and point are the other way round in German to English. When I changed them manually the added figures at the bottom of the column vanished.

When I right-click on the cell with the number in it, I can find my way to a "format cell" box where I could choose "customised" (Benutzerdefiniert) for the numbers, but there are several options, such as "#.##0,00DM_-;" etc. I fiddled around with them until the number appeared as 20.00 but either the columns didn't want to add up any more, or it just appeared as "20".

There's also an "options" box but you can't customise the numbers there.


Direct link Reply with quote
 
xxxMichaelRS
Local time: 07:38
Tools - options - international Jul 11, 2005

In the English version, it's Tools - Options - International.

You can then set the separators ( . or , ).

The problem will be when someone with Excel in a different configuration gets the file. You have to tell them about it, because Excel will only recognize numbers in the format you have set.

If numbers are written as text, you can just change them - you don't have to worry about it.


Direct link Reply with quote
 
Arthur Allmendinger  Identity Verified
Germany
Local time: 07:38
English to German
+ ...
Try it with a csv-file Jul 11, 2005

Anne Koth wrote:

I'm sure this must be very simple, but how do I change the comma in my German Excel 97 into a decimal point but so that it still adds up the numbers in the columns?

[Edited at 2005-07-11 14:41]


Try to export the spreadsheet to a csv-file, replace the commas with points in this text-file with a text editor and then import this text-file with excel. If you have a German version of Excel, you have to change the country specific settings (Dezimalzeichen).


Direct link Reply with quote
 

Lars Jelking  Identity Verified
Israel
Local time: 08:38
Member (2006)
English to Swedish
+ ...
Regional settings. Jul 11, 2005

It seems to me the setting is done for the format with comma. You should try to change it from the Control Panel/Date, Time .../Change format of numbers ..... . Look there and se what the default is. You may have to change it to fit the data You are working with.

And remember: You can always set it back!!


Direct link Reply with quote
 

Kurt Porter  Identity Verified
Local time: 11:38
Russian to English
+ ...
Some of these may help Jul 11, 2005

Anne Koth wrote:

I don't know Excel at all yet, so am rather confused too...

I tried changing the comma into a decimal point manually, but i guess it doesn't recognise them as numbers, as the comma and point are the other way round in German to English. When I changed them manually the added figures at the bottom of the column vanished.

When I right-click on the cell with the number in it, I can find my way to a "format cell" box where I could choose "customised" (Benutzerdefiniert) for the numbers, but there are several options, such as "#.##0,00DM_-;" etc. I fiddled around with them until the number appeared as 20.00 but either the columns didn't want to add up any more, or it just appeared as "20".

There's also an "options" box but you can't customise the numbers there.


http://www.exceltip.com/
http://www.mistupid.com/tutorials/excel/
http://exceltips.vitalnews.com/

Good luck!


Direct link Reply with quote
 
Rebekka Groß  Identity Verified
Local time: 06:38
English to German
how's the file itself set up? Jul 11, 2005

I also think it's to do with the global settings.

You can check the way numbers are formatted in cells or whole columns. Depends on whether a particular column contains numbers only.

In Excel, highlight the cell/column you want to change. If you press the Ctrl key while highlighting you can select several non consecutive cells/columns, Shift selects consecutive items. But of course, if they have different formatting, you might end up even more confused. I'd therefore suggest you only highlight one of the cells in questions.

Then, select Cells from the Format menu and on the Numbers tab under Category select either Number or Currency.

In the Negative Numbers list the currently selected decimal character is used. If the numbers have a decimal point instead of a comma, it's due to the Global Settings, which you can change as Carlos explained earlier.

Not sure if this helps or confuses you even more - it's very hot here and my brain is slighly fried.

Also, I've got a German bersion of Excel so the names of UI items might not be 100%.

HTH.

Rebekka


Direct link Reply with quote
 

Lars Jelking  Identity Verified
Israel
Local time: 08:38
Member (2006)
English to Swedish
+ ...
Reginal settings. Jul 11, 2005

Anne, have You looked at the regional settings in Your operating system (Windows?)? You find it on the Control Panel / Date, Time ... / Change the format .... . There You can customize the separators to what is required.
I have English as default language, so it may be different on Your computer.


Direct link Reply with quote
 

Anne Koth  Identity Verified
Germany
Local time: 07:38
German to English
TOPIC STARTER
got it, thanks! Jul 11, 2005

Good news, I've now worked out what the control panel is.(Systemsteuerung: I've never had an English Windows, pardon my stupidity!) That does seem to be the only answer. I guess I'll have to make it into a PDF to keep the formatting.

I couldn't find any tips about this particular problem on those tip sites, although the one with the Flash movies is nice, but if anyone else in Germany has the same problem they might be interested in this German tip: http://www.cad-school.ch/TNT/Software/Excel/Dezimal-tausend-zeichen-aendern.html From Excel 2002 it seems it wouldn't be a problem.


Direct link Reply with quote
 
xxxdavy dirkx
English
Had similar problem, and figured it out May 22, 2008

I wanted to make an excel-file with some cells just having times in it (eg 3.53.24 for 3 minutes 53 and so on) but it changed my POINT on the NUM part of the keyboard to a COMMA, and I thought that just looked horrible (3,53,24 - iew!) I had that problem before and never found a solution, so if anyone else has the same experience, here's the answer (quite simple, silly of me I never thought of it before)

Don't know the correct translations of the things in english, but I'll try:
Go to extra and Autocorrection, and add under "replace text while typing" "," with "."

That's it !!!
Aaah, I hate microsoft... why do they think nobody would ever want to use points instead of commas?


Direct link Reply with quote
 
junkboy
Germany
Local time: 07:38
English
How to change comma to decimal in Excel Oct 21, 2008

Using XP and Excel 2000...
Start -> Einstellungen -> Systemsteuerung (to get to Control Panel)
Then double-click Ländereinstellungen (to adjust regional settings)
Click on tab called Zahlen.
Overwrite the field called "Dezimaltrennzeichen" with a decimal point.
Overwrite the field called "Symbol für Zifferngruppierung" with a comma.
Click Übernehmen to apply the changes.
Click OK.
Excel will now display decimal points, and calculate as desired.


Direct link Reply with quote
 

Timothy Barton
Local time: 08:38
French to English
+ ...
Not necessary to change Windows settings May 6, 2015

You don't have to change Windows settings. You can change Excel settings. Here's an explanation for one of the more modern Excel versions, but even in the old versions I believe this was possible.

http://www.solveyourtech.com/change-decimal-separator-excel-2013/

However, please note this is only any use if you're going to copy/paste one of the tables somewhere. Changing the Windows or Excel settings on your own computer will not effect how your client sees them. If you're simply translating an Excel file you can just hand it in as it is, without changing the decimal separator. However, if your client lives in a country where the decimal separator used is different to that used by the language of your translation you should warn the client to change the settings on their computer.

[Edited at 2015-05-06 14:12 GMT]


Direct link Reply with quote
 
Terry Richards
France
Local time: 07:38
French to English
+ ...
Thanks Timothy May 6, 2015

I'm running an English version of Excel on a French version of Windows and that stupid comma instead of a decimal point has been driving me slowly loopy.

Direct link Reply with quote
 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Excel: turning comma into decimal point 20,00 -> 20.00

Advanced search






CafeTran Espresso
You've never met a CAT tool this clever!

Translate faster & easier, using a sophisticated CAT tool built by a translator / developer. Accept jobs from clients who use SDL Trados, MemoQ, Wordfast & major CAT tools. Download and start using CafeTran Espresso -- for free

More info »
PerfectIt consistency checker
Faster Checking, Greater Accuracy

PerfectIt helps deliver error-free documents. It improves consistency, ensures quality and helps to enforce style guides. It’s a powerful tool for pro users, and comes with the assurance of a 30-day money back guarantee.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search