Currency formatting

[Guest]

Hello,

How can I use find and replace to find a number ( eg. 10, 100, 1,000, 10.2,
10.55, etc) and replace that with a currency formatted version of the number
(eg. $10.00, $1,000.00, $10.20, etc).

Any help or suggestions appreciated.

 

[Tony Jollans]

Find and Replace doesn't have that kind of functionality. You will need some
VBA code - something along these lines should do it ...

Selection.Find.ClearFormatting
With Selection.Find
.Text = "[!0-9.,][0-9.,]{1,}[!0-9]"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
While Selection.Find.Execute
With Selection
.MoveStart wdCharacter, 1
.MoveEnd wdCharacter, -1
.Text = Format(Selection.Text, "$#,###,###.00")
.Collapse wdCollapseEnd
End With
Wend

 

[Guest]

Hi Tony,

Thanks for the response, for reasons I won't bore you with I am going to
have to get around this without using a macro. Would you have any other
suggestions on how to get around this problem?
--
Thanks,
MarkN

Find and Replace doesn't have that kind of functionality. You will need some
VBA code - something along these lines should do it ...

Selection.Find.ClearFormatting
With Selection.Find
.Text = "[!0-9.,][0-9.,]{1,}[!0-9]"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
While Selection.Find.Execute
With Selection
.MoveStart wdCharacter, 1
.MoveEnd wdCharacter, -1
.Text = Format(Selection.Text, "$#,###,###.00")
.Collapse wdCollapseEnd
End With
Wend

 

[Anne Troy]

Mark, is this stuff by chance in a table? If so, why not copy to Excel,
format and copy back?
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

Hi Tony,

Thanks for the response, for reasons I won't bore you with I am going to
have to get around this without using a macro. Would you have any other
suggestions on how to get around this problem?
--
Thanks,
MarkN

Find and Replace doesn't have that kind of functionality. You will need
some
VBA code - something along these lines should do it ...

Selection.Find.ClearFormatting
With Selection.Find
.Text = "[!0-9.,][0-9.,]{1,}[!0-9]"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
While Selection.Find.Execute
With Selection
.MoveStart wdCharacter, 1
.MoveEnd wdCharacter, -1
.Text = Format(Selection.Text, "$#,###,###.00")
.Collapse wdCollapseEnd
End With
Wend

--
Enjoy,
Tony

Hello,

How can I use find and replace to find a number ( eg. 10, 100, 1,000, 10.2,
10.55, etc) and replace that with a currency formatted version of the number
(eg. $10.00, $1,000.00, $10.20, etc).

Any help or suggestions appreciated.

 

[Guest]

Hi Anne,

Most of the numbers are contained in tables and I currently do exactly what
you suggested but there are several tables, other numbers throughout the
document, some which need formatting some which don't.

If I keep using the Excel method for the stuff in tables, I would like to
know how to set up a wildcard search for "numbers" in a document, ie. what
you type in the find box to find any number regardless of value.

--
Thanks,
MarkN

Mark, is this stuff by chance in a table? If so, why not copy to Excel,
format and copy back?
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

Hi Tony,

Thanks for the response, for reasons I won't bore you with I am going to
have to get around this without using a macro. Would you have any other
suggestions on how to get around this problem?
--
Thanks,
MarkN

Find and Replace doesn't have that kind of functionality. You will need
some
VBA code - something along these lines should do it ...

Selection.Find.ClearFormatting
With Selection.Find
.Text = "[!0-9.,][0-9.,]{1,}[!0-9]"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
While Selection.Find.Execute
With Selection
.MoveStart wdCharacter, 1
.MoveEnd wdCharacter, -1
.Text = Format(Selection.Text, "$#,###,###.00")
.Collapse wdCollapseEnd
End With
Wend

--
Enjoy,
Tony


Hello,

How can I use find and replace to find a number ( eg. 10, 100, 1,000,
10.2,
10.55, etc) and replace that with a currency formatted version of the
number
(eg. $10.00, $1,000.00, $10.20, etc).

Any help or suggestions appreciated.

 

[Graham Mayor]

The wildcard search pattern is that indicated in Tony's macro, but you can't
replace this with the currency format without using the macro. See
http://www.gmayor.com/replace_using_wildcards.htm

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

My web site www.gmayor.com

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

Hi Anne,

Most of the numbers are contained in tables and I currently do
exactly what you suggested but there are several tables, other
numbers throughout the document, some which need formatting some
which don't.

If I keep using the Excel method for the stuff in tables, I would
like to know how to set up a wildcard search for "numbers" in a
document, ie. what you type in the find box to find any number
regardless of value.

Mark, is this stuff by chance in a table? If so, why not copy to
Excel, format and copy back?
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

Hi Tony,

Thanks for the response, for reasons I won't bore you with I am
going to have to get around this without using a macro. Would you
have any other suggestions on how to get around this problem?
--
Thanks,
MarkN


:

Find and Replace doesn't have that kind of functionality. You will
need some
VBA code - something along these lines should do it ...

Selection.Find.ClearFormatting
With Selection.Find
.Text = "[!0-9.,][0-9.,]{1,}[!0-9]"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
While Selection.Find.Execute
With Selection
.MoveStart wdCharacter, 1
.MoveEnd wdCharacter, -1
.Text = Format(Selection.Text, "$#,###,###.00")
.Collapse wdCollapseEnd
End With
Wend

--
Enjoy,
Tony


Hello,

How can I use find and replace to find a number ( eg. 10, 100,
1,000, 10.2,
10.55, etc) and replace that with a currency formatted version of
the number (eg. $10.00, $1,000.00, $10.20, etc).

Any help or suggestions appreciated.

 

[Guest]

Hi Graham,

Thanks for the link.
--
Thanks again,
MarkN

The wildcard search pattern is that indicated in Tony's macro, but you can't
replace this with the currency format without using the macro. See
http://www.gmayor.com/replace_using_wildcards.htm

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

My web site www.gmayor.com

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

Hi Anne,

Most of the numbers are contained in tables and I currently do
exactly what you suggested but there are several tables, other
numbers throughout the document, some which need formatting some
which don't.

If I keep using the Excel method for the stuff in tables, I would
like to know how to set up a wildcard search for "numbers" in a
document, ie. what you type in the find box to find any number
regardless of value.

Mark, is this stuff by chance in a table? If so, why not copy to
Excel, format and copy back?
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

Hi Tony,

Thanks for the response, for reasons I won't bore you with I am
going to have to get around this without using a macro. Would you
have any other suggestions on how to get around this problem?
--
Thanks,
MarkN


:

Find and Replace doesn't have that kind of functionality. You will
need some
VBA code - something along these lines should do it ...

Selection.Find.ClearFormatting
With Selection.Find
.Text = "[!0-9.,][0-9.,]{1,}[!0-9]"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
While Selection.Find.Execute
With Selection
.MoveStart wdCharacter, 1
.MoveEnd wdCharacter, -1
.Text = Format(Selection.Text, "$#,###,###.00")
.Collapse wdCollapseEnd
End With
Wend

--
Enjoy,
Tony


Hello,

How can I use find and replace to find a number ( eg. 10, 100,
1,000, 10.2,
10.55, etc) and replace that with a currency formatted version of
the number (eg. $10.00, $1,000.00, $10.20, etc).

Any help or suggestions appreciated.

 

[Tony Jollans]

If you want to just use Find and Replace without code you are going to be
restricted - and forced to use an awkward multi-step process.

Off the top of my head, I think you should (all untested) ...

a) remove commas from numbers
Find: ,([0-9]{3,3})
Replace \1
b) add .00 to numbers not followed by a decimal point
Find ([0-9]{1,})([!.])
Replace \1.00\2
c) special case of above for numbers at the ends of sentences
Find ([0-9]{1,}).([!0-9])
Replace \1.00.\2
d) add a trailing zero to numbers with a single decimal places
Find ([0-9]{1,}.[0-9])([!0-9])
Replace \10\2
e) truncate more than 2 decimal places (you can't round)
Find ([0-9].[0-9][0-9])[0-9]{1,}
Replace \1
f) reinsert commas for thousands
Find ([0-9])([0-9]{3,3}.)
Replace \1,\2
g) reinsert commas for millions
Find ([0-9])([0-9]{3,3},)
Replace \1,\2
h) repeat above for billions, trillions, etc
i) insert leading dollar signs.
Find "([!0-9.,])([0-9.,]{1,}[!0-9])
Replace \1$\2

You may be able to optimise this with your knowledge of the actual numbers
in your document.

--
Enjoy,
Tony

Hi Anne,

Most of the numbers are contained in tables and I currently do exactly what
you suggested but there are several tables, other numbers throughout the
document, some which need formatting some which don't.

If I keep using the Excel method for the stuff in tables, I would like to
know how to set up a wildcard search for "numbers" in a document, ie. what
you type in the find box to find any number regardless of value.

--
Thanks,
MarkN

Mark, is this stuff by chance in a table? If so, why not copy to Excel,
format and copy back?
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

Hi Tony,

Thanks for the response, for reasons I won't bore you with I am going to
have to get around this without using a macro. Would you have any other
suggestions on how to get around this problem?
--
Thanks,
MarkN


:

Find and Replace doesn't have that kind of functionality. You will need
some
VBA code - something along these lines should do it ...

Selection.Find.ClearFormatting
With Selection.Find
.Text = "[!0-9.,][0-9.,]{1,}[!0-9]"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
While Selection.Find.Execute
With Selection
.MoveStart wdCharacter, 1
.MoveEnd wdCharacter, -1
.Text = Format(Selection.Text, "$#,###,###.00")
.Collapse wdCollapseEnd
End With
Wend

--
Enjoy,
Tony


Hello,

How can I use find and replace to find a number ( eg. 10, 100, 1,000,
10.2,
10.55, etc) and replace that with a currency formatted version of the
number
(eg. $10.00, $1,000.00, $10.20, etc).

Any help or suggestions appreciated.