Changing Periods To Commas for European Currency

**Robatmass** · 03-27-2010, 02:04 PM

Hi,

My company financial reports are UK based so use periods in currency cells, but I am currently based in Finland with a Finnish PC so my copy of XL uses Commasfor currency cells.

So when I download the company reports from iuf web admin the currency cells show as a date rather than a currency (because the Finnish version takes the periods as the space between date days/months etc).

Does anyone know how I can convert all these cells into currency cells?

**martindwilson** · 03-27-2010, 02:07 PM

can you use an intermediate step? say get into note pad and use find replace then import what does the raw data look like?

**Robatmass** · 03-27-2010, 02:26 PM

Originally Posted by martindwilson

can you use an intermediate step? say get into note pad and use find replace then import what does the raw data look like?

These are stock and sales reports for our web store. So each report has over 1500 products and over 10 columns... So that solution may be difficult..

**martindwilson** · 03-27-2010, 03:41 PM

try this, in control panel change settings to English
import /save/ close
change settings back to Finnish reopen work book
i just did it in reverse, imported a text file
15,3
16,1
with settings as Finnish

then did =a1+a2 got 31,4
saved/ closed
changed to english
reopened now i get
15.3
16.1 31.4

**zbor** · 03-27-2010, 03:48 PM

How about aditional column with substitute:

=SUBSTITUTE(A1;".";",")

**martindwilson** · 03-27-2010, 03:52 PM

that'll be 10 columns then lol
you could do each number column with find replace find . replace ,
but changing the regional settings will sort out any other delimiters as well

**Marcol** · 03-27-2010, 03:59 PM

Have you tried setting the decimal separator options in Excel?

In Tools > Options > International Tab

1/. uncheck "use system separators"

2/. set Decimal separator "." Thousands separator ","

3/. Import your file

4/. check "use system separators"

No need to change the separators back as they were, leave them as your second choice.

After that you can toggle between the two systems while the file is open.

Don't quite see where the date formatting comes from though, but this should make the file import English style and hopefully the problem might not occur.