| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Currency formatting
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. -- Thanks, MarkN |
|
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Currency formatting
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 "MarkN" wrote in message ... 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. -- Thanks, MarkN |
|
#3
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Currency formatting
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 "Tony Jollans" wrote: 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 "MarkN" wrote in message ... 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. -- Thanks, MarkN |
|
#4
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Currency formatting
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 "MarkN" wrote in message ... 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 "Tony Jollans" wrote: 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 "MarkN" wrote in message ... 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. -- Thanks, MarkN |
|
#5
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Currency formatting
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 "Anne Troy" wrote: 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 "MarkN" wrote in message ... 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 "Tony Jollans" wrote: 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 "MarkN" wrote in message ... 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. -- Thanks, MarkN |
|
#6
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Currency formatting
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 Word MVP web site http://word.mvps.org MarkN wrote: 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 "MarkN" wrote in message ... 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 "Tony Jollans" wrote: 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 "MarkN" wrote in message ... 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. -- Thanks, MarkN |
|
#7
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Currency formatting
Hi Graham,
Thanks for the link. -- Thanks again, MarkN "Graham Mayor" wrote: 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 Word MVP web site http://word.mvps.org MarkN wrote: 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 "MarkN" wrote in message ... 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 "Tony Jollans" wrote: 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 "MarkN" wrote in message ... 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. -- Thanks, MarkN |
|
#8
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Currency formatting
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 "MarkN" wrote in message ... 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 "Anne Troy" wrote: 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 "MarkN" wrote in message ... 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 "Tony Jollans" wrote: 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 "MarkN" wrote in message ... 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. -- Thanks, MarkN |
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatic Formatting in Word/Office XP | Microsoft Word Help | |||
| Formatting In Use (Style and Formatting Task Pane) Slow + Incorrect? | Formatting Long Documents | |||
| Form Fields lose bold formatting | Microsoft Word Help | |||
| Currency in merge from Excel | Microsoft Word Help | |||
| Apply Style but keep direct formatting? | New Users | |||
Linear Mode
