Quickly replace any characters in Excel (such as accented chars, new lines, non-printable chars, etc.)
Normally if you need to replace accented characters with normal characters in Excel, you have to repeatedly use the Find and Replace function for replacing each accented character. But with the Replace Any Characters utility of Kutools for Excel, you can easily replace any accented characters with regular chars at once.
This feature also provides some other useful options, such as: converting between Chinese punctuation and English punctuation marks, converting between Full-width chars and Half-width chars, converting between uppercase and lowercase letters, converting between line breaks and spaces and removing non-printing characters. If these rules don’t meet your requirement, you can create custom rules for replacing any chars as you need.
Replace accented characters with regular characters
Convert between line breaks and spaces
Remove non-printable characters from cells
Create custom rules for replacing any characters with specific characters
Click Kutools >> Text >> Replace Any Characters. See screenshots:
Replace accented characters with regular characters
To convert the accented characters to regular characters, please do as this:
1. Select a range of cells where you want to replace the accented characters.
2. Then, click Kutools > Text > Replace Any Characters to open the Replace Any Characters dialog box. In the dialog box, please do the following operations:
- A: Select Accented characters from the Scenario drop down list;
- B: The corresponding rules are displayed into the Rules list box, you can add new rules, edit or remove the current rules as you need;
- C: At the same time, you can preview the results from the Preview pane.
3. Then, click Ok or Apply, the accented characters you specified have been replaced by the regular characters. See screenshots:
- Add: Click this button to add a new rule into this scenario;
- Edit: Select one rule from the list box, and click this button to modify the rule you need;
- Swap: This button is used to toggle between the Find what and Replace with contents;
- Remove: Click this button to remove the rules that you don’t want;
- Reset: If the rules changed, you can click this button to revert to the default settings.
Convert between line breaks and spaces
If you want to convert line breaks (which starts a new line of text in a cell) to spaces or vice versa, please follow the below steps:
1. Select the cells that contains line breaks, and then click Kutools > Text > Replace Any Characters to enable this feature.
2. In the Replace Any Characters dialog box, please do the following operations:
- A: Select Newlines and spaces from the Scenario drop down list;
- B: The corresponding rule is displayed into the list box;
- C: At the same time, you can preview the results from the Preview pane.
3. And then, click Ok or Apply button, the newlines are replaced with spaces, see screenshots:
- Add: Click this button to add a new rule into this scenario;
- Edit: Select one rule from the list box, and click this button to modify the rule you need;
- Swap: This button is used to toggle between the Find what and Replace with contents;
- Remove: Click this button to remove the rules that you don’t want;
- Reset: If the rules changed, you can click this button to revert to the default settings.
Remove non-printable characters from cells
In Excel, non-printable characters are used to indicate certain formatting actions, such as carriage return, line feed, etc., or the characters in the ASCII and Unicode character sets.
Sometimes, when importing data from external sources to Excel, some various non-printing characters will be displayed in the worksheet. To remove these unwanted characters, this feature can help you as well.
1. Select the cells that you want to use, and then click Kutools > Text > Replace Any Characters to enable this feature.
2. In the Replace Any Characters dialog box, please do the following operations:
- A: Select Remove non-printing characters from the Scenario drop down list;
- B: The corresponding rules are displayed into the list box, and all the rules are checked by default;
- C: You can preview the results from the Preview pane.
3. Then, click Ok or Apply button, all the non-printing characters are replaced with blanks. See screenshots:
- Add: Click this button to add a new rule into this scenario;
- Edit: Select one rule from the list box, and click this button to modify the rule you need;
- Swap: This button is used to toggle between the Find what and Replace with contents;
- Remove: Click this button to remove the rules that you don’t want;
- Reset: If the rules changed, you can click this button to revert to the default settings.
Create custom rules for replacing any characters with specific characters
If these scenarios are not what you need, you can create your own rules for replacing any characters with specific characters. Please do with the following steps:
1. Go to the Replace Any Characters dialog box by clicking Kutools > Text > Replace Any Characters, in the dialog box, select New scenario from the Scenario drop down list, see screenshot:
2. In the popped-out prompt box, please type a scenario name, see screenshot:
3. Then, click Ok to return to the main dialog box. Now, click Add button to add rules you need, see screenshot:
4. In the Add rule dialog box:
- (1) In the Find content list box, type the text that you want to find. If there are multiple text you want to replace with the same characters, please press Enter key to separate the values.
- (2) In the Replace with textbox, enter the text that you want to replace with.
- (3) Click Ok to go back to the main Replace Any Characters dialog, and the new rule is added into the list box as below screenshot shown:
5. After creating this rule, you can apply this rule to replace the specified values to a certain value you need as below screenshot shown:
- Add: Click this button to add a new rule into this scenario;
- Edit: Select one rule from the list box, and click this button to modify the rule you need;
- Swap: This button is used to toggle between the Find what and Replace with contents;
- Remove: Click this button to remove the rules that you don’t want;
- Reset: If the rules changed, you can click this button to revert to the default settings.
- 1. This feature supports Undo (Ctrl + Z).
- 2. You can manage the scenarios by clicking the Manage Scenario icon
in the Replace Any Characters dialog box. In the Manage Scenario dialog box, you can add new scenarios, rename and copy scenarios, delete the newly created scenarios as you need. See screenshot:
in the Replace Any Characters dialog box. In the Manage Scenario dialog box, you can add new scenarios, rename and copy scenarios, delete the newly created scenarios as you need. See screenshot:
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.
Kutools for Excel
The functionality described above is just one of 300 powerful functions of Kutools for Excel.
Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 30 days.
