Displaying to types of punctuation i one sheet

[Simon]

Hii all
I would like to display a result with EU punctuation and US punctuation in
the same sheet but in different coloumns. Ex.

EU: 3.438,23
US: 3,438.23

Is that possible?
When i try, it converts all data in to the same punctuation
I use excel 2003 european version

Thanks for any help

 

[Stefi]

Only as text: if A1 has a value of 3.438,23 as number then this formula
returns 3,438.23 as text.

=SUBSTITUTE(LEFT(TEXT(A1,"#.##0,00"),LEN(TEXT(A1,"#.##0,00"))-3),".",",")&"."&RIGHT(TEXT(A1,"#.##0,00"),2)

Regards,
Stefi

„Simon†ezt írta:

 

[Simon]

The formula does not seem to work. But thanks anyway.

I need it as numbers. So is the only solution to have 2 different
excel-books or is it possible to operate with to types of punctuation in 2
different sheets?

Again thanks for any help

/Simon

 

[Stefi]

You have to use to separate workbooks and set decimal separator in
Tools>Options>Language tab (I'm not sure that Language is the tab name, I'm
translating it back, it's located between Color and Save).

Regards,
Stefi

„Simon†ezt írta:

 

[Gord Dibben]

Stefi

I think you are referring to Tools>Options>International in Excel 2003.

That is an Excel global setting, not a workbook setting.


Gord Dibben MS Excel MVP

 

[Simon]

Ok...

That is to bad, means a lot of extra work for me.

But thanks again.

If anyone has a alternative solution to this problem, you´re very welcome to
post it.

Thanks

/Simon

 

[Stefi]

Sorry, I didn't check it. I thought that unchecking system settings makes a
single workbook independent of systems setings and not the whole Excel
application. Thank you for correcting my fault.

Stefi


„Gord Dibben†ezt írta: