Replace Chinese and English Punctuation in Excel – Step-by-Step Guide

When working with data in Microsoft Excel, you may often encounter inconsistent punctuation, such as a mix of Chinese and English symbols within the same worksheet. This issue is not limited to multilingual datasets—it commonly occurs when data is imported from different systems, copied from web pages, or entered by multiple users using different input methods.

For example, a dataset may contain commas like ，and ,, or parentheses like （ and ( in the same column. This inconsistency can lead to problems when sorting data, performing text analysis, exporting files, or simply maintaining a clean and professional format.

In practice, users often need to convert Chinese punctuation to English, or perform the reverse—replace English punctuation with Chinese equivalents. This guide walks you through several reliable methods, starting from built-in Excel features, then formulas and VBA, and finally a much more efficient solution using Kutools for Excel.

• Replace Chinese and English Punctuation Using Find and Replace
• Use Excel Formulas to Replace Punctuation
• Convert Chinese and English Punctuation in Bulk with Kutools for Excel

Replace Chinese and English Punctuation Using Find and Replace

The most straightforward way to replace punctuation in Excel is by using the built-in Find and Replace feature. This method is suitable for small datasets or when you only need to replace a few punctuation marks.

Steps

1. Select the range of cells that contains the punctuations you want to modify.
2. Then press Ctrl + H to open the Find and Replace dialog box.
3. In the Find and Replace dialog box, you need to:
   1. Enter the punctuation you want to replace in the Find what field, such as a Chinese period 。.
   2. Type the corresponding English punctuation in the Replace with field, for example, a period ..
   3. Click Replace All.

      

Result

Excel will update all matching punctuation within the selected range.

While this method is easy to understand and requires no additional tools, it quickly becomes inefficient when dealing with many different punctuation symbols. You need to manually repeat the operation multiple times, and it’s easy to miss certain characters, especially in large datasets.

Use Excel Formulas to Replace Punctuation

If you want a more dynamic approach, Excel formulas can help automate the replacement process. The most commonly used function for this task is SUBSTITUTE, which replaces specific text within a string.

1. Select a blank cell next to your data. Enter the following formula:

   =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"，",","),"。","."),"（","("),"）",")"),"！","!")

2. Press Enter and drag its Fill Handle down to get the rest of the results.

   

This formula replaces multiple Chinese punctuation marks with their English equivalents in one step.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,",","，"),".","。"),"(","（"),")","）"),"!","！")

This approach is useful when you need to keep the transformation dynamic, meaning the result updates automatically if the source data changes.

However, as the number of punctuation marks increases, the formula becomes longer and harder to manage. It can quickly turn into a complex nested structure that is difficult to read, debug, or modify. For large-scale replacements, this method is not ideal.

Convert Chinese and English Punctuation in Bulk with Kutools for Excel

If you are looking for a faster and more reliable way to replace Chinese and English punctuation, using Kutools for Excel is highly recommended. Compared with built-in features or formulas, this method is much more intuitive and allows you to handle multiple punctuation marks in a single operation.

Kutools provides a dedicated tool called Replace Any Characters, which comes with pre-defined punctuation rules and a real-time preview panel, making the entire process straightforward and efficient.

Steps

1. Select the range of cells that contains the text you want to process.
2. Go to Kutools → Text → Replace Any Characters.

   

3. In the Replace Any Characters dialog box,
   1. Choose the scenario Chinese and English punctuation marks from the drop-down list.
   2. You will see a list of built-in punctuation rules.
      If needed, you can click Add to include additional punctuation rules based on your specific requirements.
   3. Click OK to perform the replacement.

      

Result

All specified punctuation marks will be replaced instantly across the selected range.

Tips:

• By default, this scenario converts Chinese punctuation to English.
• If you want to reverse the operation (English to Chinese), simply click the Swap button to instantly switch all rules—no need to recreate them manually.

Unlike other methods, Kutools eliminates the need for repetitive steps or complex formulas. You can replace multiple punctuation marks at once, preview the results before applying, and even customize the rules as needed—all within a few clicks.

Conclusion

Replacing Chinese and English punctuation in Excel can be done in several ways, but each comes with trade-offs. Built-in features are simple but repetitive, formulas can become complex, and VBA is not always reliable for everyday use.

For a faster and more practical solution—especially when handling large datasets—Kutools for Excel allows you to replace multiple punctuation marks at once with just a few clicks.