How to convert 1,000.00 into 1.000,00?

[s80NL]

How can I convert a price formatted by as 1,000.00 into 1.000,00?

I need the 1.000,00 format to be able to see the prices in my pivot
table as "Sum of Price". Of course, 1000,00 will be fine as well.

 

[Guest]

Isn't that a regional setting? (TaskBar) Start-->Settings-->Control
Panel-->Regional and Language Options

 

[Gord Dibben]

Or possibly Tools>Options>International.

Uncheck "use system separators" and change to what you wish.


Gord Dibben MS Excel MVP

Isn't that a regional setting? (TaskBar) Start-->Settings-->Control
Panel-->Regional and Language Options

 

[s80NL]

Thanks for your replies, I guess I haven't explained the situation
enough to let you know that this can't be done with just a change of
settings.

From SAP I get a Excel-dump with lot's of data, prices can be formatted
as 1,000.00 as well as 1.000,00 depending on the users SAP settings.
Besides this we also have PSP-elements which are formatted like 1234.56
and the should keep that format.

The question I should have asked you yesterday should have been: How
can I recognize a 1,000.00 format AND when it is formatted in that way,
how can I convert it into a 1.000,00 format?

FYI, the Excel-dump from SAP does contain the actual , as thousand
seperator in the datafield.

Hope you can help me now again

 

[NickHK]

Sounds like you getting the data as text rather than numerical.
If it is a string you could check a format, you could
Dim FormattedValue as string
If InputValue = Format(CLng(InputValue), "#,##0.00) Then
'It is in 1,000.00 format

But if it actually number, but appears in one format or another because of
Excel then
If Range("A1").Text=Range("A1").Value Then

Some of the formatting options will depend on your Regional Setting.

NickHK

 

[s80NL]

NickHK, that's right it's a data string. I will have a go with your
hints. Thanks for now!!