Changing Decimal Commas to Decimal Points

by Allen Wyatt
(last updated December 12, 2015)

6

Sometimes Flavio has to "localize" scientific text written in Argentina or Spain, which uses decimal commas as delimiters, into "Mexican" Spanish, which uses the decimal point. There are hundreds of such instances where Flavio has to make the change. He wonders if there is a way to use Find and Replace to make such a change.

The short answer is that, yes, there is a way to do this using Find and Replace. How you use Find and Replace to do this, however, depends on the nature of the numbers you are working with.

Many countries use periods and commas in their numbers opposite of the way they are in other countries. For instance, many people will recognize the meaning of numbers such as this:

1,234,567.89

In other countries, however, the number would be shown in this manner:

1.234.567,89

Notice that the purpose of the commas and periods are exactly opposite in these two instances. In one case commands are used as "thousands separators" and in the other as decimal points. In one case the period is used as a decimal point and in the other as a thousands separator.

If your numbers are smaller—under 1,000—you may not need to worry about thousands separators. If this is the case (as it seems to be with Flavio's problem description), then you can use a single wildcard find and replace to do the conversion. Follow these steps:

  1. Press Ctrl+H to display the Replace tab of the Find and Replace dialog box.
  2. Click on the More button if it is available.
  3. Select the Use Wildcards check box.
  4. In the Find What box enter "<([0-9]*),([0-9]*)>", without the quote marks.
  5. In the Replace With box enter "\1.\2", again without the quote marks.
  6. Click on the Replace All button.

The pattern you used in step 4 tells Word that you want to find any number of digits at the beginning of a word (that's what the < character means) followed by a comma and then any number of digits at the end of a word. Note the use of parentheses in the search pattern—they are included so that whatever is found that matches the pattern inside the parentheses can be referenced in the replacement pattern. The preplacement pattern then uses these groups (note there are two sets of parentheses) to say that the first group (\1) should be followed by a period and then the second group (\2).

If your source numbers include periods as thousands separators, then you don't want to do the find and replace as just discussed. If you do, you'll end up with periods in both the thousands separators and the decimal point. Instead, you need to do three individual find and replace operations. In the first one you'll change the thousands separators (the existing periods) to something other than a period or a comma. In the second one you'll change the decimal comma to a decimal point. In the third one you'll then change the "neutral" character you used in the first find and replace to the commas. Here's a way you can do the steps:

  1. Press Ctrl+H to display the Replace tab of the Find and Replace dialog box.
  2. Click on the More button if it is available.
  3. Select the Use Wildcards check box.
  4. In the Find What box enter "([0-9]*).([0-9]*)", without the quote marks.
  5. In the Replace With box enter "\1xyz\2", again without the quote marks. (Note that I'm using the lowercase "xyz" as my neutral delimiter.)
  6. Click on the Replace All button.
  7. In the Find What box enter "([0-9]*),([0-9]*)", without the quote marks.
  8. In the Replace With box enter "\1.\2", again without the quote marks.
  9. Click on the Replace All button.
  10. In the Find What box enter "([0-9]*)xyz([0-9]*)", without the quote marks.
  11. In the Replace With box enter "\1,\2", again without the quote marks.
  12. Click on the Replace All button.

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (10061) applies to Microsoft Word 2007, 2010, 2013, and 2016.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Turning Off Document Protection

If you protect your document using the tools that Word provides, at some time you may need to turn off that protection. ...

Discover More

Automatic Row Height For Merged Cells with Text Wrap

When you have text wrap turned on in a cell, Excel expands the height of the row as you add more text to the cell. When you ...

Discover More

Positioning the Footnote Separator

The Footnote Separator, as its name implies, separates the footnotes on each page from the text on that page. If the ...

Discover More

The First and Last Word on Word! Bestselling For Dummies author Dan Gookin puts his usual fun and friendly candor back to work to show you how to navigate Word 2013. Spend more time working and less time trying to figure it all out! Check out Word 2013 For Dummies today!

More WordTips (ribbon)

Getting Rid of Hidden Text in Many Files

Hidden text is a great boon if you want to make sure something doesn't show up on the screen or on a printout. If you want to ...

Discover More

Easily Spotting Found Text

The color used by Word to draw your attention to "found text" when using Find and Replace can vary, based on a number of ...

Discover More

Searching for Text that Doesn't End a Paragraph

The Find and Replace capabilities of Word are very powerful, particularly if you are using wildcards in your search. Here's ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 + 0?

2017-05-24 17:28:20

adnan unay

([0-9]*).([0-9]{3,3}[!0-9]) did not work for me.
Can you please check if it really works?


2017-04-12 16:49:06

Gravel

Your tip just save my thesis. Thanks!


2016-12-10 13:37:03

Emiliano

Thank you very much! It saved me the pain of doing this manually! I am working on a thesis with a bunch of tables that are output of econometric models. I hope it will be published in English. Kind regards from Argentina!


2016-10-11 07:56:38

Gabriela Cirstea

I had to change decimal commas into decimal points for around three thousand decimal numbers as part of the translation work I am doing.
Your clear and detailed instructions helped me a lot.
Thank you very much!


2015-12-13 00:41:15

Ken Endacott

The Find and replace commands given will not discriminate between a genuine number and for example a manual line number such as:

3.1 This is a manually numbered paragraph

What is required is to search for a period that is followed by exactly 3 numerals.

The Find string in step 4 of the second part should be "([0-9]*).([0-9]{3,3}[!0-9])"


2015-12-12 20:49:31

Muhammad S Attari

Nice Tip. Thanks


This Site

Got a version of Word that uses the ribbon interface (Word 2007 or later)? This site is for you! If you use an earlier version of Word, visit our WordTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.