Help with Excel column maths symbols Italian to English Thread poster: Paul O'Brien
|
|---|
Paul O'Brien 
Argentina
Local time: 19:46
Member (2007)
Italian to English
+ ...
Hi. I work with Microsoft 2004 on Mac OSX. I'm translating a price list from Italian to English, and in the last colum to the right the prices are in Euro, but with commas in the place of points, and vice-versa. When I go to change them the whole column goes haywire. What do I need to do to make the necessary switch? TIA.
| | | |
Claudio Porcellana (X)
Italy
try to use the International and language options in MS Windows (control panel)
or select the Excel column and choose another option for it:
right click/Cell format/General instead of Number or similar
Claudio
| | | |
Oliver Walter
United Kingdom
Local time: 23:46
German to English
+ ...
| some possible thoughts | Aug 8, 2009 |
I can't answer your exact problem (I'm using Excel 2000 and Windows XP) but the following might give you some useful ideas.
1. Brute-force method: force the contents of the affected cells to be treated as text and type the exact text you want. to do this, put an apostrophe at the start of the cell.
Example: for "twelve thousand three hundred and forty-five point six eight", your Excel presumably shows this as 12345,68 but let's say you want 12,345.68. For that, e... See more I can't answer your exact problem (I'm using Excel 2000 and Windows XP) but the following might give you some useful ideas.
1. Brute-force method: force the contents of the affected cells to be treated as text and type the exact text you want. to do this, put an apostrophe at the start of the cell.
Example: for "twelve thousand three hundred and forty-five point six eight", your Excel presumably shows this as 12345,68 but let's say you want 12,345.68. For that, enter the following into the cell:
'12,345.68
This should look correct but will be left-justified, which you can then adjust (Format >Cells alignment). Also, Excel won't regard it as a number and you can't use it in formulae that require a number.
2. Your version of Excel may have an "international" tab in Tools > Options, which should allow this change.
3. In Windows (but perhaps OSX doesn't have the equivalent) you can change the "regional" settings in the Control Panel and achieve the required result, and the number will still be a number.
I hope that helps or might at least give you a helpful idea.
Oliver ▲ Collapse
| | | |
Paul O'Brien
Argentina
Local time: 19:46
Member (2007)
Italian to English
+ ...
TOPIC STARTER
Thanks fellas but a) my "Tools" bar has no "Options", b) when I selected the column, right-clicked and set to General, figures such as 450,00 (euro) just became 450, with no decimal point. There are over 1,000 rows so I don't want to change them all by hand and c) I have no MS Control Panel option.
| | |
|
|
|
| Maybe this will work | Aug 8, 2009 |
I'm a newbie when it comes to Mac and I've got MS Office 2008, but maybe this will work on 2004 as well.
To find International you have to go to the Apple menu and then click System preferences and in the top row of icons you should see a flag and the title International. Click on this and the Formats at the top of the new window where you will find a regional drop down menu.
Hope it helps as I haven't yet had time to try this out.
| | | |
Paul O'Brien
Argentina
Local time: 19:46
Member (2007)
Italian to English
+ ...
TOPIC STARTER | did it, but doesnt work | Aug 8, 2009 |
Hi Madeleine, I did as you suggested and in the preferences all the points changed to commas and vice versa. But that Excel column hasn't budged an inch.
| | | |
| Think I've got it now | Aug 8, 2009 |
I created a new workbook and entered a number in English format (comma for thousands, dot for decimal) and saved it. Then with the doc still open I changed to Swedish format (space for thousands, comma for decimal). Nothing happened at first, but then I closed Excel and restarted it. Then when I open the file it's changed.
| | | |
Paul O'Brien
Argentina
Local time: 19:46
Member (2007)
Italian to English
+ ...
TOPIC STARTER
|
|
|
Oliver Walter
United Kingdom
Local time: 23:46
German to English
+ ...
| Method using a formula | Aug 8, 2009 |
Here's a method that will probably work. It seems very complicated but it works in my Excel. This will occupy further cells to the right (or left, if you choose) of the cells containing the prices. You'll then need to (a) use "fill down" or copy to replicate the formula with the cell references automatically adjusted, and (b) hide the cells containing the original numbers.
Suppose a number like 12345,68 (i.e continental format with decimal comma) is in cell B8 and you want cell C8 t... See more Here's a method that will probably work. It seems very complicated but it works in my Excel. This will occupy further cells to the right (or left, if you choose) of the cells containing the prices. You'll then need to (a) use "fill down" or copy to replicate the formula with the cell references automatically adjusted, and (b) hide the cells containing the original numbers.
Suppose a number like 12345,68 (i.e continental format with decimal comma) is in cell B8 and you want cell C8 to show 12345.68. Put the following formula into cell C8:
=TEXT(TRUNC(B8,0),"0")&"."&MID(TEXT(B8-TRUNC(B8,0),".00"),2,2)
and you should get the desired result. This formula concatenates three strings:
- the integer part of the number ( TEXT(TRUNC(B8,0),"0") )
- a string consisting of the point ( "." )
- a string consisting of the fractional part (2 decimal places:
MID(TEXT(B8-TRUNC(B8,0),".00"),2,2) . In this, the first "2" tells MID to start at the second character of the string it's processing, and the second "2" tells it to supply two characters, i.e. the two to the right of the decimal point.
I couldn't find a simpler way since Excel doesn't contains a FRAC function to return the fractional part of a number.
(I've seen Madeleine's method after posting mine - mine might be of interest for somebody for some purpose at some time!)
Oliver
[Edited at 2009-08-08 11:45 GMT] ▲ Collapse
| | | |
Login to reply/comment 
