Excel: turning comma into decimal point 20,00 -> 20.00 Thread poster: Anne Brackenborough (X)
|
|---|
Anne Brackenborough (X) 
Germany
Local time: 07:53
German to English
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]
| | | | | 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...
| | | | Can Altinbay
Local time: 01:53
Japanese to English
+ ...
In memoriam | 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.
| | | | Anne Brackenborough (X)
Germany
Local time: 07:53
German to English
TOPIC STARTER
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... See more 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. ▲ Collapse
| | |
|
|
|
MichaelRS (X)
Local time: 07:53
| 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.
| | | | | 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).
| | | | Lars Jelking
Israel
Local time: 08:53
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!!
| | | | | 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!
| | |
|
|
|
Rebekka Groß (X)
Local time: 06:53
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 s... See more 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 ▲ Collapse
| | | | Lars Jelking
Israel
Local time: 08:53
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.
| | | | Anne Brackenborough (X)
Germany
Local time: 07:53
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... See more 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. ▲ Collapse
| | | | | 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:
... See more 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? ▲ Collapse
| | |
|
|
|
junkboy
Germany
Local time: 07:53
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 des... See more 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.
 ▲ Collapse
| | | | | 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 ta... See more 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] ▲ Collapse
| | | | | 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.
| | | | 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 | Protemos translation business management system | Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!
The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.
More info » |
| | TM-Town | Manage your TMs and Terms ... and boost your translation business
Are you ready for something fresh in the industry? TM-Town is a unique new site for you -- the freelance translator -- to store, manage and share translation memories (TMs) and glossaries...and potentially meet new clients on the basis of your prior work.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
