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.

[Charlie]

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

"s80NL" wrote:

>
> 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.
>
>
> --
> s80NL
>
>
> ------------------------------------------------------------------------
> s80NL's Profile: http://www.excelforum.com/member.php...o&userid=36374
> View this thread: http://www.excelforum.com/showthread...hreadid=562479
>
>

[Gord Dibben]

Or possibly Tools>Options>International.

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


Gord Dibben MS Excel MVP

On Tue, 18 Jul 2006 11:14:01 -0700, Charlie <Charlie@discussions.microsoft.com>
wrote:

>Isn't that a regional setting? (TaskBar) Start-->Settings-->Control
>Panel-->Regional and Language Options
>
>"s80NL" wrote:
>
>>
>> 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.
>>
>>
>> --
>> s80NL
>>
>>
>> ------------------------------------------------------------------------
>> s80NL's Profile: http://www.excelforum.com/member.php...o&userid=36374
>> View this thread: http://www.excelforum.com/showthread...hreadid=562479
>>
>>

[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" <s80NL.2b6if1_1153289704.5714@excelforum-nospam.com> wrote in
message news:s80NL.2b6if1_1153289704.5714@excelforum-nospam.com...
>
> 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
>
>
> --
> s80NL
>
>
> ------------------------------------------------------------------------
> s80NL's Profile:
http://www.excelforum.com/member.php...o&userid=36374
> View this thread: http://www.excelforum.com/showthread...hreadid=562479
>

[s80NL]

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