Help with Excel column maths symbols Italian to English
Thread poster: Paul O'Brien

Paul O'Brien  Identity Verified
Italy
Local time: 04:19
Italian to English
Aug 8, 2009

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.

Direct link Reply with quote
 

Claudio Porcellana  Identity Verified
Italy
help Aug 8, 2009

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


Direct link Reply with quote
 

Oliver Walter  Identity Verified
United Kingdom
Local time: 03:19
Member (2005)
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, 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


Direct link Reply with quote
 

Paul O'Brien  Identity Verified
Italy
Local time: 04:19
Italian to English
TOPIC STARTER
nope Aug 8, 2009

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.

Direct link Reply with quote
 

Madeleine MacRae Klintebo  Identity Verified
United Kingdom
Local time: 03:19
Swedish to English
+ ...
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.


Direct link Reply with quote
 

Paul O'Brien  Identity Verified
Italy
Local time: 04:19
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.

Direct link Reply with quote
 

Madeleine MacRae Klintebo  Identity Verified
United Kingdom
Local time: 03:19
Swedish to English
+ ...
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.

Direct link Reply with quote
 

Paul O'Brien  Identity Verified
Italy
Local time: 04:19
Italian to English
TOPIC STARTER
yessssssssss Aug 8, 2009

Yes!

Direct link Reply with quote
 

Oliver Walter  Identity Verified
United Kingdom
Local time: 03:19
Member (2005)
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 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:

  1. the integer part of the number ( TEXT(TRUNC(B8,0),"0") )
  2. a string consisting of the point ( "." )
  3. 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]


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 »

Help with Excel column maths symbols Italian to English

Advanced search






SDL MultiTerm 2017
Guarantee a unified, consistent and high-quality translation with terminology software by the industry leaders.

SDL MultiTerm 2017 allows translators to create one central location to store and manage multilingual terminology, and with SDL MultiTerm Extract 2017 you can automatically create term lists from your existing documentation to save time.

More info »
Déjà Vu X3
Try it, Love it

Find out why Déjà Vu is today the most flexible, customizable and user-friendly tool on the market. See the brand new features in action: *Completely redesigned user interface *Live Preview *Inline spell checking *Inline

More info »



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