Field, Numeric: decimal point AND comma format ?

[Lotiana]

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!

 

[Graham Mayor]

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Lotiana]

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]

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Lotiana]

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]

Unfortunately that isn't possible with fields because the separators are
determined by Windows regional settings.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Graham Mayor]

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Graham Mayor]

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Lotiana]

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