Problem With European Formatting of Amounts

[Tiziano]

I have received a spreadsheet from Europe which has periodS for the 1000's
separator and commas for the decimals separator (i.e. $1.234,56 instead of
$1,234.56
as commonly used in the USA). To compound the problem, the figures have a
single quote
in front which I believe makes them text instead of number format...
I am unable to perform any calculations with this kind of format! What can
I do to remedy the situation? (I am unable to get whoever sent me the file
to re-send it
in some other format.)

Things I have tried:
* Used Edit -> Replace -> Replace All
It does not work because I need to replace TWO items instead of one
(commas instead of periods and vice-versa).
* Entered figure "1" (without quotes) in a separate cell, copied that cell
to the
clipboard, highlighted the entire range of text numbers and selected
Paste Special -> Multiply.

Thanks for any tips.

 

[Peo Sjoblom]

You could do a replace twice, edit>replace find what .(period)
replace with (leave that part blank)
and again find what , (comma)
replace with . (period)
then copy an empty cell and select the values
and do edit>paste special and check add.
Finally format as currency

Or you could use a help column and a formula and copy down

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

format as currency

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Tiziano]

Replacing the period with nothing and the comma with a period works very
well!
Thanks for your efforts.

What is the function of the "--" that you have placed between the "=" and
"SUBSTITUTE"?
----
Tiziano

You could do a replace twice, edit>replace find what .(period)
replace with (leave that part blank)
and again find what , (comma)
replace with . (period)
then copy an empty cell and select the values
and do edit>paste special and check add.
Finally format as currency

Or you could use a help column and a formula and copy down

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

format as currency

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Peo Sjoblom]

To force calculation and convert the text into a number
you could also use 0+ or 1*

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Replacing the period with nothing and the comma with a period works very
well!
Thanks for your efforts.

What is the function of the "--" that you have placed between the "=" and
"SUBSTITUTE"?
----
Tiziano

You could do a replace twice, edit>replace find what .(period)
replace with (leave that part blank)
and again find what , (comma)
replace with . (period)
then copy an empty cell and select the values
and do edit>paste special and check add.
Finally format as currency

Or you could use a help column and a formula and copy down

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

format as currency

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

instead

of

have