Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
Field, Numeric: decimal point AND comma format ?
How can I get in the same table both GBP 9,999.99 and EUR 9.999,99 ?
"Regional settings" either/or don't help! |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
Field, Numeric: decimal point AND comma format ?
How is Word to know whether the number is GBP or EUR?
Are the relative values of the currencies relevant? We need more information about exactly what you are trying to achieve. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: How can I get in the same table both GBP 9,999.99 and EUR 9.999,99 ? "Regional settings" either/or don't help! |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
Field, Numeric: decimal point AND comma format ?
Graham, it's about the very unsophisticated invoices I send from the UK to
our clients on the Continental Europe - a small table I'd like to keep Word-only. So far, I "compromised": one column displays GBP 1,000.00, the corresponding one EUR 1,600.00 (with number format {=SUM(ABOVE) \# "EUR #,##0.00"}). With so many beautiful functions/features of Word, I would have thought that the other format may co-exist too (like a column with formulas with the other number format, displaying EUR 1.600,00 with, say, {=SUM(ABOVE) \# "EUR #.##0,00"}). Sorry for bothering you, Graham, I don't want to waste your time; not bad if not possible: clients got used to it anyway (otherwise I'd had used Excel); I just wanted to imitate instantly the double display in Amazon.co.uk's invoices, and to write to a supplier from the Continent a letter with a calculation without changing first Regional settings in Control Panel. But I'm only doing this sort of work rarely, it's not among my main tasks. "Graham Mayor" wrote: How is Word to know whether the number is GBP or EUR? Are the relative values of the currencies relevant? We need more information about exactly what you are trying to achieve. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: How can I get in the same table both GBP 9,999.99 and EUR 9.999,99 ? "Regional settings" either/or don't help! |
#4
Posted to microsoft.public.word.tables
|
|||
|
|||
Field, Numeric: decimal point AND comma format ?
It's not a bother, but an intriguing challenge of the type that some of us
thrive on However, I am still not clear on whether you want the table to make the conversion from Sterling to Euros, and if you do what you are going to use as an exchange rate, which as you know goes up and down like a bride's nightie. If you are creating invoices for British and European clients, then it shouldn't be too difficult to use to ensure that you have the right prices on your documents. It's a little more difficult if you want to only have the right currency and the correct value of that currency shown, but it is not impossible. If you use an ASK field for the exchange rate eg { Ask Rate "Exchange Rate" \d "1.3056448789"} then { ={ =SUM(LEFT) } * { Rate } \# "EUR ,0.00"} in the cell to the right of the GBP total will give you the Euro amount (assuming there are no other amounts on that row). -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: Graham, it's about the very unsophisticated invoices I send from the UK to our clients on the Continental Europe - a small table I'd like to keep Word-only. So far, I "compromised": one column displays GBP 1,000.00, the corresponding one EUR 1,600.00 (with number format {=SUM(ABOVE) \# "EUR #,##0.00"}). With so many beautiful functions/features of Word, I would have thought that the other format may co-exist too (like a column with formulas with the other number format, displaying EUR 1.600,00 with, say, {=SUM(ABOVE) \# "EUR #.##0,00"}). Sorry for bothering you, Graham, I don't want to waste your time; not bad if not possible: clients got used to it anyway (otherwise I'd had used Excel); I just wanted to imitate instantly the double display in Amazon.co.uk's invoices, and to write to a supplier from the Continent a letter with a calculation without changing first Regional settings in Control Panel. But I'm only doing this sort of work rarely, it's not among my main tasks. "Graham Mayor" wrote: How is Word to know whether the number is GBP or EUR? Are the relative values of the currencies relevant? We need more information about exactly what you are trying to achieve. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: How can I get in the same table both GBP 9,999.99 and EUR 9.999,99 ? "Regional settings" either/or don't help! |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
Field, Numeric: decimal point AND comma format ?
Hi again Graham,
What I'd like to do is about "static" representation/display rather than dynamic/real-time computation with the instant exchange rate, namely our here usual \# "EUR ,0.00" for some fields, and their \# "EUR .0,00" (decimal point is comma) for others. "Graham Mayor" wrote: It's not a bother, but an intriguing challenge of the type that some of us thrive on However, I am still not clear on whether you want the table to make the conversion from Sterling to Euros, and if you do what you are going to use as an exchange rate, which as you know goes up and down like a bride's nightie. If you are creating invoices for British and European clients, then it shouldn't be too difficult to use to ensure that you have the right prices on your documents. It's a little more difficult if you want to only have the right currency and the correct value of that currency shown, but it is not impossible. If you use an ASK field for the exchange rate eg { Ask Rate "Exchange Rate" \d "1.3056448789"} then { ={ =SUM(LEFT) } * { Rate } \# "EUR ,0.00"} in the cell to the right of the GBP total will give you the Euro amount (assuming there are no other amounts on that row). -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: Graham, it's about the very unsophisticated invoices I send from the UK to our clients on the Continental Europe - a small table I'd like to keep Word-only. So far, I "compromised": one column displays GBP 1,000.00, the corresponding one EUR 1,600.00 (with number format {=SUM(ABOVE) \# "EUR #,##0.00"}). With so many beautiful functions/features of Word, I would have thought that the other format may co-exist too (like a column with formulas with the other number format, displaying EUR 1.600,00 with, say, {=SUM(ABOVE) \# "EUR #.##0,00"}). Sorry for bothering you, Graham, I don't want to waste your time; not bad if not possible: clients got used to it anyway (otherwise I'd had used Excel); I just wanted to imitate instantly the double display in Amazon.co.uk's invoices, and to write to a supplier from the Continent a letter with a calculation without changing first Regional settings in Control Panel. But I'm only doing this sort of work rarely, it's not among my main tasks. "Graham Mayor" wrote: How is Word to know whether the number is GBP or EUR? Are the relative values of the currencies relevant? We need more information about exactly what you are trying to achieve. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: How can I get in the same table both GBP 9,999.99 and EUR 9.999,99 ? "Regional settings" either/or don't help! |
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
Field, Numeric: decimal point AND comma format ?
Unfortunately that isn't possible with fields because the separators are
determined by Windows regional settings. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: Hi again Graham, What I'd like to do is about "static" representation/display rather than dynamic/real-time computation with the instant exchange rate, namely our here usual \# "EUR ,0.00" for some fields, and their \# "EUR .0,00" (decimal point is comma) for others. "Graham Mayor" wrote: It's not a bother, but an intriguing challenge of the type that some of us thrive on However, I am still not clear on whether you want the table to make the conversion from Sterling to Euros, and if you do what you are going to use as an exchange rate, which as you know goes up and down like a bride's nightie. If you are creating invoices for British and European clients, then it shouldn't be too difficult to use to ensure that you have the right prices on your documents. It's a little more difficult if you want to only have the right currency and the correct value of that currency shown, but it is not impossible. If you use an ASK field for the exchange rate eg { Ask Rate "Exchange Rate" \d "1.3056448789"} then { ={ =SUM(LEFT) } * { Rate } \# "EUR ,0.00"} in the cell to the right of the GBP total will give you the Euro amount (assuming there are no other amounts on that row). -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: Graham, it's about the very unsophisticated invoices I send from the UK to our clients on the Continental Europe - a small table I'd like to keep Word-only. So far, I "compromised": one column displays GBP 1,000.00, the corresponding one EUR 1,600.00 (with number format {=SUM(ABOVE) \# "EUR #,##0.00"}). With so many beautiful functions/features of Word, I would have thought that the other format may co-exist too (like a column with formulas with the other number format, displaying EUR 1.600,00 with, say, {=SUM(ABOVE) \# "EUR #.##0,00"}). Sorry for bothering you, Graham, I don't want to waste your time; not bad if not possible: clients got used to it anyway (otherwise I'd had used Excel); I just wanted to imitate instantly the double display in Amazon.co.uk's invoices, and to write to a supplier from the Continent a letter with a calculation without changing first Regional settings in Control Panel. But I'm only doing this sort of work rarely, it's not among my main tasks. "Graham Mayor" wrote: How is Word to know whether the number is GBP or EUR? Are the relative values of the currencies relevant? We need more information about exactly what you are trying to achieve. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: How can I get in the same table both GBP 9,999.99 and EUR 9.999,99 ? "Regional settings" either/or don't help! |
#7
Posted to microsoft.public.word.tables
|
|||
|
|||
Field, Numeric: decimal point AND comma format ?
This has been bugging me The best I can come up with so far is:
{ Set A "{ =INT({ =SUM(ABOVE) })}" }{ Set B "{ ={ =SUM(ABOVE) } - { =INT({ =SUM(ABOVE) }) } }" }{ IF{ A } 999999 "{ A \# "EUR #'.'###'.'###" }" "{ IF{ A } 999 "{ A \# "EUR #'.'###" }" "{ A \# "EUR #" }" }" }{ ={ B } * 100 \# "','00"} which will format the total i.e. { =SUM(ABOVE) } in Euro format on UK Regional windows. It should also be possible to fix the fields in a table as text then present the numbers in Euro format using vba. If I come up with a solution to that I will post back. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Graham Mayor wrote: Unfortunately that isn't possible with fields because the separators are determined by Windows regional settings. Lotiana wrote: Hi again Graham, What I'd like to do is about "static" representation/display rather than dynamic/real-time computation with the instant exchange rate, namely our here usual \# "EUR ,0.00" for some fields, and their \# "EUR .0,00" (decimal point is comma) for others. "Graham Mayor" wrote: It's not a bother, but an intriguing challenge of the type that some of us thrive on However, I am still not clear on whether you want the table to make the conversion from Sterling to Euros, and if you do what you are going to use as an exchange rate, which as you know goes up and down like a bride's nightie. If you are creating invoices for British and European clients, then it shouldn't be too difficult to use to ensure that you have the right prices on your documents. It's a little more difficult if you want to only have the right currency and the correct value of that currency shown, but it is not impossible. If you use an ASK field for the exchange rate eg { Ask Rate "Exchange Rate" \d "1.3056448789"} then { ={ =SUM(LEFT) } * { Rate } \# "EUR ,0.00"} in the cell to the right of the GBP total will give you the Euro amount (assuming there are no other amounts on that row). -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: Graham, it's about the very unsophisticated invoices I send from the UK to our clients on the Continental Europe - a small table I'd like to keep Word-only. So far, I "compromised": one column displays GBP 1,000.00, the corresponding one EUR 1,600.00 (with number format {=SUM(ABOVE) \# "EUR #,##0.00"}). With so many beautiful functions/features of Word, I would have thought that the other format may co-exist too (like a column with formulas with the other number format, displaying EUR 1.600,00 with, say, {=SUM(ABOVE) \# "EUR #.##0,00"}). Sorry for bothering you, Graham, I don't want to waste your time; not bad if not possible: clients got used to it anyway (otherwise I'd had used Excel); I just wanted to imitate instantly the double display in Amazon.co.uk's invoices, and to write to a supplier from the Continent a letter with a calculation without changing first Regional settings in Control Panel. But I'm only doing this sort of work rarely, it's not among my main tasks. "Graham Mayor" wrote: How is Word to know whether the number is GBP or EUR? Are the relative values of the currencies relevant? We need more information about exactly what you are trying to achieve. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: How can I get in the same table both GBP 9,999.99 and EUR 9.999,99 ? "Regional settings" either/or don't help! |
#8
Posted to microsoft.public.word.tables,microsoft.public.word.vba.general
|
|||
|
|||
Field, Numeric: decimal point AND comma format ?
OK, not quite a eureka moment, but the following should work:
It formats numbers in the format #.## or # in the third table column (set at iCol = 3) to use either GBP or EUR currency separators. Calculated fields are updated and converted to text before formatting. I have crossposted to the vba general forum in case someone there has a simpler solution. Sub FormatColumnAsCurrency() Dim cTable As Table Dim oRng As Range Dim sNum, sCent, sEuro, sTEuro, sMEuro As String Dim sSep, sDec, sCurr, sSign As String Dim iCol As Integer Dim i As Long Dim count As Variant 'Set column number containing the amounts iCol = 3 sCurr = InputBox("Enter Y to format table as Euro" & vbCr & _ "Enter anything else to format as Sterling", _ "Format currency", "Y") Set cTable = ActiveDocument.Tables(1) For i = 1 To cTable.Rows.count Set oRng = cTable.Cell(i, iCol).Range On Error Resume Next With oRng.Fields .Update .Unlink End With Next i For i = 1 To cTable.Rows.count Set oRng = cTable.Cell(i, iCol).Range sNum = Replace(oRng, Chr(13) & Chr(7), "") sNum = Replace(sNum, ",", "") If InStr(1, sNum, ".") = False Then sNum = sNum & ".00" End If If UCase(sCurr) = "Y" Then sSep = "." sDec = "," sSign = "EUR " Else sSep = "," sDec = "." sSign = "GBP " End If sCent = sDec & Right(sNum, 2) sEuro = Right(Int(sNum), 3) If sNum 999999 Then sMEuro = Right(Int(sNum / 1000000), 3) & sSep Else sMEuro = "" End If If sNum 999 Then sTEuro = Right(Int(sNum / 1000), 3) & sSep Else sTEuro = "" End If sResult = Replace(oRng, sNum, sSign & sMEuro & sTEuro & sEuro & sCent) sResult = sSign & sMEuro & sTEuro & sEuro & sCent oRng = Replace(sResult, Chr(13), "") Next i End Sub -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Graham Mayor wrote: This has been bugging me The best I can come up with so far is: { Set A "{ =INT({ =SUM(ABOVE) })}" }{ Set B "{ ={ =SUM(ABOVE) } - { =INT({ =SUM(ABOVE) }) } }" }{ IF{ A } 999999 "{ A \# "EUR #'.'###'.'###" }" "{ IF{ A } 999 "{ A \# "EUR #'.'###" }" "{ A \# "EUR #" }" }" }{ ={ B } * 100 \# "','00"} which will format the total i.e. { =SUM(ABOVE) } in Euro format on UK Regional windows. It should also be possible to fix the fields in a table as text then present the numbers in Euro format using vba. If I come up with a solution to that I will post back. Graham Mayor wrote: Unfortunately that isn't possible with fields because the separators are determined by Windows regional settings. Lotiana wrote: Hi again Graham, What I'd like to do is about "static" representation/display rather than dynamic/real-time computation with the instant exchange rate, namely our here usual \# "EUR ,0.00" for some fields, and their \# "EUR .0,00" (decimal point is comma) for others. "Graham Mayor" wrote: It's not a bother, but an intriguing challenge of the type that some of us thrive on However, I am still not clear on whether you want the table to make the conversion from Sterling to Euros, and if you do what you are going to use as an exchange rate, which as you know goes up and down like a bride's nightie. If you are creating invoices for British and European clients, then it shouldn't be too difficult to use to ensure that you have the right prices on your documents. It's a little more difficult if you want to only have the right currency and the correct value of that currency shown, but it is not impossible. If you use an ASK field for the exchange rate eg { Ask Rate "Exchange Rate" \d "1.3056448789"} then { ={ =SUM(LEFT) } * { Rate } \# "EUR ,0.00"} in the cell to the right of the GBP total will give you the Euro amount (assuming there are no other amounts on that row). -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: Graham, it's about the very unsophisticated invoices I send from the UK to our clients on the Continental Europe - a small table I'd like to keep Word-only. So far, I "compromised": one column displays GBP 1,000.00, the corresponding one EUR 1,600.00 (with number format {=SUM(ABOVE) \# "EUR #,##0.00"}). With so many beautiful functions/features of Word, I would have thought that the other format may co-exist too (like a column with formulas with the other number format, displaying EUR 1.600,00 with, say, {=SUM(ABOVE) \# "EUR #.##0,00"}). Sorry for bothering you, Graham, I don't want to waste your time; not bad if not possible: clients got used to it anyway (otherwise I'd had used Excel); I just wanted to imitate instantly the double display in Amazon.co.uk's invoices, and to write to a supplier from the Continent a letter with a calculation without changing first Regional settings in Control Panel. But I'm only doing this sort of work rarely, it's not among my main tasks. "Graham Mayor" wrote: How is Word to know whether the number is GBP or EUR? Are the relative values of the currencies relevant? We need more information about exactly what you are trying to achieve. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: How can I get in the same table both GBP 9,999.99 and EUR 9.999,99 ? "Regional settings" either/or don't help! |
#9
Posted to microsoft.public.word.tables,microsoft.public.word.vba.general
|
|||
|
|||
Field, Numeric: decimal point AND comma format ?
You are an angel, Graham, what I call a PROFESSIONAL!
As soon as I get over the trouble with installing VISTA SP1 (a whole day of failures already and counting...blush...blush...blush...) I'll apply it; you'll be the first to hear. Thank you so much "Graham Mayor" wrote: OK, not quite a eureka moment, but the following should work: It formats numbers in the format #.## or # in the third table column (set at iCol = 3) to use either GBP or EUR currency separators. Calculated fields are updated and converted to text before formatting. I have crossposted to the vba general forum in case someone there has a simpler solution. Sub FormatColumnAsCurrency() Dim cTable As Table Dim oRng As Range Dim sNum, sCent, sEuro, sTEuro, sMEuro As String Dim sSep, sDec, sCurr, sSign As String Dim iCol As Integer Dim i As Long Dim count As Variant 'Set column number containing the amounts iCol = 3 sCurr = InputBox("Enter Y to format table as Euro" & vbCr & _ "Enter anything else to format as Sterling", _ "Format currency", "Y") Set cTable = ActiveDocument.Tables(1) For i = 1 To cTable.Rows.count Set oRng = cTable.Cell(i, iCol).Range On Error Resume Next With oRng.Fields .Update .Unlink End With Next i For i = 1 To cTable.Rows.count Set oRng = cTable.Cell(i, iCol).Range sNum = Replace(oRng, Chr(13) & Chr(7), "") sNum = Replace(sNum, ",", "") If InStr(1, sNum, ".") = False Then sNum = sNum & ".00" End If If UCase(sCurr) = "Y" Then sSep = "." sDec = "," sSign = "EUR " Else sSep = "," sDec = "." sSign = "GBP " End If sCent = sDec & Right(sNum, 2) sEuro = Right(Int(sNum), 3) If sNum 999999 Then sMEuro = Right(Int(sNum / 1000000), 3) & sSep Else sMEuro = "" End If If sNum 999 Then sTEuro = Right(Int(sNum / 1000), 3) & sSep Else sTEuro = "" End If sResult = Replace(oRng, sNum, sSign & sMEuro & sTEuro & sEuro & sCent) sResult = sSign & sMEuro & sTEuro & sEuro & sCent oRng = Replace(sResult, Chr(13), "") Next i End Sub -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Graham Mayor wrote: This has been bugging me The best I can come up with so far is: { Set A "{ =INT({ =SUM(ABOVE) })}" }{ Set B "{ ={ =SUM(ABOVE) } - { =INT({ =SUM(ABOVE) }) } }" }{ IF{ A } 999999 "{ A \# "EUR #'.'###'.'###" }" "{ IF{ A } 999 "{ A \# "EUR #'.'###" }" "{ A \# "EUR #" }" }" }{ ={ B } * 100 \# "','00"} which will format the total i.e. { =SUM(ABOVE) } in Euro format on UK Regional windows. It should also be possible to fix the fields in a table as text then present the numbers in Euro format using vba. If I come up with a solution to that I will post back. Graham Mayor wrote: Unfortunately that isn't possible with fields because the separators are determined by Windows regional settings. Lotiana wrote: Hi again Graham, What I'd like to do is about "static" representation/display rather than dynamic/real-time computation with the instant exchange rate, namely our here usual \# "EUR ,0.00" for some fields, and their \# "EUR .0,00" (decimal point is comma) for others. "Graham Mayor" wrote: It's not a bother, but an intriguing challenge of the type that some of us thrive on However, I am still not clear on whether you want the table to make the conversion from Sterling to Euros, and if you do what you are going to use as an exchange rate, which as you know goes up and down like a bride's nightie. If you are creating invoices for British and European clients, then it shouldn't be too difficult to use to ensure that you have the right prices on your documents. It's a little more difficult if you want to only have the right currency and the correct value of that currency shown, but it is not impossible. If you use an ASK field for the exchange rate eg { Ask Rate "Exchange Rate" \d "1.3056448789"} then { ={ =SUM(LEFT) } * { Rate } \# "EUR ,0.00"} in the cell to the right of the GBP total will give you the Euro amount (assuming there are no other amounts on that row). -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: Graham, it's about the very unsophisticated invoices I send from the UK to our clients on the Continental Europe - a small table I'd like to keep Word-only. So far, I "compromised": one column displays GBP 1,000.00, the corresponding one EUR 1,600.00 (with number format {=SUM(ABOVE) \# "EUR #,##0.00"}). With so many beautiful functions/features of Word, I would have thought that the other format may co-exist too (like a column with formulas with the other number format, displaying EUR 1.600,00 with, say, {=SUM(ABOVE) \# "EUR #.##0,00"}). Sorry for bothering you, Graham, I don't want to waste your time; not bad if not possible: clients got used to it anyway (otherwise I'd had used Excel); I just wanted to imitate instantly the double display in Amazon.co.uk's invoices, and to write to a supplier from the Continent a letter with a calculation without changing first Regional settings in Control Panel. But I'm only doing this sort of work rarely, it's not among my main tasks. "Graham Mayor" wrote: How is Word to know whether the number is GBP or EUR? Are the relative values of the currencies relevant? We need more information about exactly what you are trying to achieve. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Lotiana wrote: How can I get in the same table both GBP 9,999.99 and EUR 9.999,99 ? "Regional settings" either/or don't help! |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I format a table column to align by decimal point? | Tables | |||
Mergefield with comma, suppress comma if field blank | Mailmerge | |||
Mailmerge numeric field format | Mailmerge | |||
numeric pictu formating merge field without rounding decimal digits? | Mailmerge | |||
Format a merged numeric field | Mailmerge |