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

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

 

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


 

Oliver Walter  Identity Verified
United Kingdom
Local time: 21:14
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


 

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

 

madak  Identity Verified
United Kingdom
Local time: 21:14
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.


 

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

 

madak  Identity Verified
United Kingdom
Local time: 21:14
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.

 

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

Yes!

 

Oliver Walter  Identity Verified
United Kingdom
Local time: 21:14
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]


 


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






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 »



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