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

Understanding the COMPARE Field

The COMPARE field is rather esoteric, but it can be helpful when you need to compare two values using fields. The result of ...

Discover More

Calculating a Group Retirement Date

Calculating a retirement date can be as simple as doing some date math to see when a person reaches a certain age. ...

Discover More

Finding Wayward Links

Combining workbooks that have cross-links to each other can offer some special challenges. This tip examines how you can get ...

Discover More

Learning Made Easy! Quickly teach yourself how to format, publish, and share your content using Word 2013. With Step by Step, you set the pace, building and practicing the skills you need, just when you need them! Check out Microsoft Word 2013 Step by Step today!

More WordTips (ribbon)

Changing the Formatting of All Instances of a Word

Need to find all the instances of a particular word and change the formatting of those instances? It's easy to do using the ...

Discover More

Searching for Adjectives and Adverbs

Searching for different types of words in your documents is a nice thing to contemplate, but it is much harder to do in ...

Discover More

Finding and Replacing Text Boxes

The Find and Replace capabilities of Word are very powerful, but they still come up a bit short when searching for some ...

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 8Mpixels. 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 7 - 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.