- To post as a guest, your comment is unpublished.· 1 years agoHi Turker,
To replace all of a certain character from a cell, you need to apply this formula =SUBSTITUTE(A1,"o","_")
How to replace nth/all occurrences of a character in string in Excel?
For example, there is a string in a cell, and you need to replace the third “o” in the string, how could you quickly solve it? And what if replacing all occurrence of this characters in the string? This article will show you the method.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
This method will introduce the easy SUBSTITUTE function to replace the nth or all occurrences of a certain character in a cell in Excel.
Select a blank cell, and type the formula =SUBSTITUTE(A1,"o","_",3) into it, and press the Enter key. See screenshot:
And now you will see the third “o” is replaced. See screenshot:
(1) In the formula =SUBSTITUTE(A1,"o","_",3), A1 is the cell you will replace string, o is the specified character you will replace, _ is the specified character you will replace with, and 3 means you will replace the 3rd occurrence of “o”.
(2) If you need to replace all occurrences of “o” with underline in the Cell A1, please apply this formula =SUBSTITUTE(A1,"o","_").
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years agoI use Office 2013 and last parameter doesn't work as intended. I didn't specify it but it works like 1 entered.Only first occurence is replaced.