[SRV]

I have a mail merge which has numbers (amounts) to be formatted for european
regional settings ( comma as decimal seperator and dot as grouping seperator).

I tried the following but it did not work.
{ MERGEFIELD "Net_Amount" \# #.###,00 }

Using MS Office 2000 on Windows XP

Appreciate you quick reply

[Doug Robbins - Word MVP]

Change the formatting in the control panel before executing the merge.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

"SRV" wrote in message
...
I have a mail merge which has numbers (amounts) to be formatted for
european
regional settings ( comma as decimal seperator and dot as grouping
seperator).

I tried the following but it did not work.
{ MERGEFIELD "Net_Amount" \# #.###,00 }

Using MS Office 2000 on Windows XP

Appreciate you quick reply

[SRV]

Thank you for the reply. This soultion will not work for me as client did not
accept this as a solution. Any other thoughts would really help us.


"Doug Robbins - Word MVP" wrote:

Change the formatting in the control panel before executing the merge.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

"SRV" wrote in message
...
I have a mail merge which has numbers (amounts) to be formatted for
european
regional settings ( comma as decimal seperator and dot as grouping
seperator).

I tried the following but it did not work.
{ MERGEFIELD "Net_Amount" \# #.###,00 }

Using MS Office 2000 on Windows XP

Appreciate you quick reply

[Graham Mayor]

If you want to format a number in contradiction of the regional settings, it
all tends to get a tad clumsy if working with fields. You could treat each
part of the number as a separate string eg 10000.00 would translate to 10
000 & .00

The following will work with amounts up to 99000 - after that you will have
to set up the number segments for hundred thousands and millions

{ Set x { MERGEFIELD Amount }
}{ SET TH { =INT({ x } / 1000) }
}{ SET HU { =INT({x }) - { =({ TH } * 1000 ) } }
}{ SET DE { =(({ x } - { =INT({ x}) }) * 100 \# "','00") } }

{ IF{x } 1000 "{ TH }."}{ HU }{ DE }


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


SRV wrote:
Thank you for the reply. This soultion will not work for me as client
did not accept this as a solution. Any other thoughts would really
help us.


"Doug Robbins - Word MVP" wrote:

Change the formatting in the control panel before executing the
merge.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my
services on a paid professional basis.

"SRV" wrote in message
...
I have a mail merge which has numbers (amounts) to be formatted for
european
regional settings ( comma as decimal seperator and dot as grouping
seperator).

I tried the following but it did not work.
{ MERGEFIELD "Net_Amount" \# #.###,00 }

Using MS Office 2000 on Windows XP

Appreciate you quick reply

[Peter Jamieson]

There are basically two ways to do it:
a. if your data source allows it, write a function (e.g. in a query)
that returns the number with all the formatting characters you want
b. Do it by splitting the number into triplets. For numbers into the
billions I think the following will do it. It's a first attempt, so I
expect there are ways to simplify and/or shorten the code. (AFAICS
Graham's approach also needs a bit of extra work to deal with the
situation where you have (say) 1.000,01, and ensure that you output 000
rather than just 0 after the ".")

{ SET ts "." }{ SET dp ","
}{ SET n { MERGEFIELD thenumber } }{ IF { =SIGN(n) } = -1 "-{ SET n {
=abs(n) }{ SET z "0"
}{ SET b { =int(n/1000000000) }
}{ IF b 0 "{ b }{ ts }{ SET n { =n-(b*1000000000) } }{ SET z "000" }"
}{ SET m { =int(n/1000000) }
}{ IF m 0 "{ m \#{ z } }{ ts }{ SET n { =n-(m*1000000) } }{ SET z "000" }"
"{ IF "{ z }" = "000" "000{ ts }"
}{ SET t { =int(n/1000) }
}{ IF t 0 "{ t \#{ z } }{ ts }{ SET n { =n-(t*1000) } }{ SET z "000" }"
"{ IF "{ z }" = "000" "000{ ts }"
}{ SET u { =int(n) }
}{ IF u = 0 "{ u \#{ z } }"
}{ SET n { =n-(u*100) } }
}{ IF n 0 "{ dp }{ n \#00 }" "{ dp }00" }

Apologies if I have transcribed that incorrectly. You can put

{ SET ts "." }{ SET dp "," }

at the beginning of the document as they set up constants. I have done
the last line the way it is so that you can choose for example whether
to output ",00" or not.

Peter Jamieson

http://tips.pjmsn.me.uk

On 03/12/2009 14:19, SRV wrote:
Thank you for the reply. This soultion will not work for me as client did not
accept this as a solution. Any other thoughts would really help us.


"Doug Robbins - Word MVP" wrote:

Change the formatting in the control panel before executing the merge.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

wrote in message
...
I have a mail merge which has numbers (amounts) to be formatted for
european
regional settings ( comma as decimal seperator and dot as grouping
seperator).

I tried the following but it did not work.
{ MERGEFIELD "Net_Amount" \# #.###,00 }

Using MS Office 2000 on Windows XP

Appreciate you quick reply

[SRV]

Huge Thanks Peter for taking time and putting this code for me. I am trying
to get this to working as copy paste does not work into Word and need to put
in all the code and braces using Ctrl+F9.

"Peter Jamieson" wrote:

There are basically two ways to do it:
a. if your data source allows it, write a function (e.g. in a query)
that returns the number with all the formatting characters you want
b. Do it by splitting the number into triplets. For numbers into the
billions I think the following will do it. It's a first attempt, so I
expect there are ways to simplify and/or shorten the code. (AFAICS
Graham's approach also needs a bit of extra work to deal with the
situation where you have (say) 1.000,01, and ensure that you output 000
rather than just 0 after the ".")

{ SET ts "." }{ SET dp ","
}{ SET n { MERGEFIELD thenumber } }{ IF { =SIGN(n) } = -1 "-{ SET n {
=abs(n) }{ SET z "0"
}{ SET b { =int(n/1000000000) }
}{ IF b 0 "{ b }{ ts }{ SET n { =n-(b*1000000000) } }{ SET z "000" }"
}{ SET m { =int(n/1000000) }
}{ IF m 0 "{ m \#{ z } }{ ts }{ SET n { =n-(m*1000000) } }{ SET z "000" }"
"{ IF "{ z }" = "000" "000{ ts }"
}{ SET t { =int(n/1000) }
}{ IF t 0 "{ t \#{ z } }{ ts }{ SET n { =n-(t*1000) } }{ SET z "000" }"
"{ IF "{ z }" = "000" "000{ ts }"
}{ SET u { =int(n) }
}{ IF u = 0 "{ u \#{ z } }"
}{ SET n { =n-(u*100) } }
}{ IF n 0 "{ dp }{ n \#00 }" "{ dp }00" }

Apologies if I have transcribed that incorrectly. You can put

{ SET ts "." }{ SET dp "," }

at the beginning of the document as they set up constants. I have done
the last line the way it is so that you can choose for example whether
to output ",00" or not.

Peter Jamieson

http://tips.pjmsn.me.uk

On 03/12/2009 14:19, SRV wrote:
Thank you for the reply. This soultion will not work for me as client did not
accept this as a solution. Any other thoughts would really help us.


"Doug Robbins - Word MVP" wrote:

Change the formatting in the control panel before executing the merge.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

wrote in message
...
I have a mail merge which has numbers (amounts) to be formatted for
european
regional settings ( comma as decimal seperator and dot as grouping
seperator).

I tried the following but it did not work.
{ MERGEFIELD "Net_Amount" \# #.###,00 }

Using MS Office 2000 on Windows XP

Appreciate you quick reply

.

[SRV]

Thank You Graham. I appreciate you time and effort. Will come to post the
results.

"Graham Mayor" wrote:

If you want to format a number in contradiction of the regional settings, it
all tends to get a tad clumsy if working with fields. You could treat each
part of the number as a separate string eg 10000.00 would translate to 10
000 & .00

The following will work with amounts up to 99000 - after that you will have
to set up the number segments for hundred thousands and millions

{ Set x { MERGEFIELD Amount }
}{ SET TH { =INT({ x } / 1000) }
}{ SET HU { =INT({x }) - { =({ TH } * 1000 ) } }
}{ SET DE { =(({ x } - { =INT({ x}) }) * 100 \# "','00") } }

{ IF{x } 1000 "{ TH }."}{ HU }{ DE }


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


SRV wrote:
Thank you for the reply. This soultion will not work for me as client
did not accept this as a solution. Any other thoughts would really
help us.


"Doug Robbins - Word MVP" wrote:

Change the formatting in the control panel before executing the
merge.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my
services on a paid professional basis.

"SRV" wrote in message
...
I have a mail merge which has numbers (amounts) to be formatted for
european
regional settings ( comma as decimal seperator and dot as grouping
seperator).

I tried the following but it did not work.
{ MERGEFIELD "Net_Amount" \# #.###,00 }

Using MS Office 2000 on Windows XP

Appreciate you quick reply


.

[SRV]

Peter,
I tried to apply the given solution into my template but did not get any
output. I haved added 2 closing braces that were missing before trying. If I
am not asking too much, can you please provide me the compiled code that I
can use without changes. I spent a day to get this working with my poor
knowledge of template code and is in vain.

Thank you so much for all your help.

"Peter Jamieson" wrote:

There are basically two ways to do it:
a. if your data source allows it, write a function (e.g. in a query)
that returns the number with all the formatting characters you want
b. Do it by splitting the number into triplets. For numbers into the
billions I think the following will do it. It's a first attempt, so I
expect there are ways to simplify and/or shorten the code. (AFAICS
Graham's approach also needs a bit of extra work to deal with the
situation where you have (say) 1.000,01, and ensure that you output 000
rather than just 0 after the ".")

{ SET ts "." }{ SET dp ","
}{ SET n { MERGEFIELD thenumber } }{ IF { =SIGN(n) } = -1 "-{ SET n {
=abs(n) }{ SET z "0"
}{ SET b { =int(n/1000000000) }
}{ IF b 0 "{ b }{ ts }{ SET n { =n-(b*1000000000) } }{ SET z "000" }"
}{ SET m { =int(n/1000000) }
}{ IF m 0 "{ m \#{ z } }{ ts }{ SET n { =n-(m*1000000) } }{ SET z "000" }"
"{ IF "{ z }" = "000" "000{ ts }"
}{ SET t { =int(n/1000) }
}{ IF t 0 "{ t \#{ z } }{ ts }{ SET n { =n-(t*1000) } }{ SET z "000" }"
"{ IF "{ z }" = "000" "000{ ts }"
}{ SET u { =int(n) }
}{ IF u = 0 "{ u \#{ z } }"
}{ SET n { =n-(u*100) } }
}{ IF n 0 "{ dp }{ n \#00 }" "{ dp }00" }

Apologies if I have transcribed that incorrectly. You can put

{ SET ts "." }{ SET dp "," }

at the beginning of the document as they set up constants. I have done
the last line the way it is so that you can choose for example whether
to output ",00" or not.

Peter Jamieson

http://tips.pjmsn.me.uk

On 03/12/2009 14:19, SRV wrote:
Thank you for the reply. This soultion will not work for me as client did not
accept this as a solution. Any other thoughts would really help us.


"Doug Robbins - Word MVP" wrote:

Change the formatting in the control panel before executing the merge.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

wrote in message
...
I have a mail merge which has numbers (amounts) to be formatted for
european
regional settings ( comma as decimal seperator and dot as grouping
seperator).

I tried the following but it did not work.
{ MERGEFIELD "Net_Amount" \# #.###,00 }

Using MS Office 2000 on Windows XP

Appreciate you quick reply

.

[Peter Jamieson]

If you despam my email
pjj at-sign pjjnet dot demon dot co dot uk
and email me I can send you what I have (I won't be around for a couple
of days so now would be a good time!)

Peter Jamieson

http://tips.pjmsn.me.uk

On 04/12/2009 20:27, SRV wrote:
Peter,
I tried to apply the given solution into my template but did not get any
output. I haved added 2 closing braces that were missing before trying. If I
am not asking too much, can you please provide me the compiled code that I
can use without changes. I spent a day to get this working with my poor
knowledge of template code and is in vain.

Thank you so much for all your help.

"Peter Jamieson" wrote:

There are basically two ways to do it:
a. if your data source allows it, write a function (e.g. in a query)
that returns the number with all the formatting characters you want
b. Do it by splitting the number into triplets. For numbers into the
billions I think the following will do it. It's a first attempt, so I
expect there are ways to simplify and/or shorten the code. (AFAICS
Graham's approach also needs a bit of extra work to deal with the
situation where you have (say) 1.000,01, and ensure that you output 000
rather than just 0 after the ".")

{ SET ts "." }{ SET dp ","
}{ SET n { MERGEFIELD thenumber } }{ IF { =SIGN(n) } = -1 "-{ SET n {
=abs(n) }{ SET z "0"
}{ SET b { =int(n/1000000000) }
}{ IF b 0 "{ b }{ ts }{ SET n { =n-(b*1000000000) } }{ SET z "000" }"
}{ SET m { =int(n/1000000) }
}{ IF m 0 "{ m \#{ z } }{ ts }{ SET n { =n-(m*1000000) } }{ SET z "000" }"
"{ IF "{ z }" = "000" "000{ ts }"
}{ SET t { =int(n/1000) }
}{ IF t 0 "{ t \#{ z } }{ ts }{ SET n { =n-(t*1000) } }{ SET z "000" }"
"{ IF "{ z }" = "000" "000{ ts }"
}{ SET u { =int(n) }
}{ IF u= 0 "{ u \#{ z } }"
}{ SET n { =n-(u*100) } }
}{ IF n 0 "{ dp }{ n \#00 }" "{ dp }00" }

Apologies if I have transcribed that incorrectly. You can put

{ SET ts "." }{ SET dp "," }

at the beginning of the document as they set up constants. I have done
the last line the way it is so that you can choose for example whether
to output ",00" or not.

Peter Jamieson

http://tips.pjmsn.me.uk

On 03/12/2009 14:19, SRV wrote:
Thank you for the reply. This soultion will not work for me as client did not
accept this as a solution. Any other thoughts would really help us.


"Doug Robbins - Word MVP" wrote:

Change the formatting in the control panel before executing the merge.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

wrote in message
...
I have a mail merge which has numbers (amounts) to be formatted for
european
regional settings ( comma as decimal seperator and dot as grouping
seperator).

I tried the following but it did not work.
{ MERGEFIELD "Net_Amount" \# #.###,00 }

Using MS Office 2000 on Windows XP

Appreciate you quick reply

.

[SRV]

I have sent a mail just now.

"Peter Jamieson" wrote:

If you despam my email
pjj at-sign pjjnet dot demon dot co dot uk
and email me I can send you what I have (I won't be around for a couple
of days so now would be a good time!)

Peter Jamieson

http://tips.pjmsn.me.uk

On 04/12/2009 20:27, SRV wrote:
Peter,
I tried to apply the given solution into my template but did not get any
output. I haved added 2 closing braces that were missing before trying. If I
am not asking too much, can you please provide me the compiled code that I
can use without changes. I spent a day to get this working with my poor
knowledge of template code and is in vain.

Thank you so much for all your help.

"Peter Jamieson" wrote:

There are basically two ways to do it:
a. if your data source allows it, write a function (e.g. in a query)
that returns the number with all the formatting characters you want
b. Do it by splitting the number into triplets. For numbers into the
billions I think the following will do it. It's a first attempt, so I
expect there are ways to simplify and/or shorten the code. (AFAICS
Graham's approach also needs a bit of extra work to deal with the
situation where you have (say) 1.000,01, and ensure that you output 000
rather than just 0 after the ".")

{ SET ts "." }{ SET dp ","
}{ SET n { MERGEFIELD thenumber } }{ IF { =SIGN(n) } = -1 "-{ SET n {
=abs(n) }{ SET z "0"
}{ SET b { =int(n/1000000000) }
}{ IF b 0 "{ b }{ ts }{ SET n { =n-(b*1000000000) } }{ SET z "000" }"
}{ SET m { =int(n/1000000) }
}{ IF m 0 "{ m \#{ z } }{ ts }{ SET n { =n-(m*1000000) } }{ SET z "000" }"
"{ IF "{ z }" = "000" "000{ ts }"
}{ SET t { =int(n/1000) }
}{ IF t 0 "{ t \#{ z } }{ ts }{ SET n { =n-(t*1000) } }{ SET z "000" }"
"{ IF "{ z }" = "000" "000{ ts }"
}{ SET u { =int(n) }
}{ IF u= 0 "{ u \#{ z } }"
}{ SET n { =n-(u*100) } }
}{ IF n 0 "{ dp }{ n \#00 }" "{ dp }00" }

Apologies if I have transcribed that incorrectly. You can put

{ SET ts "." }{ SET dp "," }

at the beginning of the document as they set up constants. I have done
the last line the way it is so that you can choose for example whether
to output ",00" or not.

Peter Jamieson

http://tips.pjmsn.me.uk

On 03/12/2009 14:19, SRV wrote:
Thank you for the reply. This soultion will not work for me as client did not
accept this as a solution. Any other thoughts would really help us.


"Doug Robbins - Word MVP" wrote:

Change the formatting in the control panel before executing the merge.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

wrote in message
...
I have a mail merge which has numbers (amounts) to be formatted for
european
regional settings ( comma as decimal seperator and dot as grouping
seperator).

I tried the following but it did not work.
{ MERGEFIELD "Net_Amount" \# #.###,00 }

Using MS Office 2000 on Windows XP

Appreciate you quick reply

.

.