How to Use the REPLACE, REPLACEBs Function in Excel
Replace Text in Excel and Google Sheets
In both Microsoft Excel and Google Sheets, the REPLACE function allows you to substitute a specific portion of a text string with a different text. This function is particularly handy for modifying text within a cell or removing unwanted characters from a string.
Basic Syntax:
The syntax for the REPLACE function is consistent across both Excel and Google Sheets:
| Argument | Description |
|---|---|
| old_text | The original text where you intend to make a replacement. |
| start_num | The position in old_text where the replacement will begin. |
| num_chars | The number of characters in old_text to be replaced. |
| new_text | The text that will be inserted in place of the specified segment of old_text. |
Examples:
Here are some examples to demonstrate how the REPLACE function can be applied in practical scenarios.
Example 1:
Imagine you have the phrase “Good morning, World!” in cell A1 and you wish to replace “morning” with “afternoon”. Use the formula:
=REPLACE(A1, 6, 7, "afternoon")This formula will substitute “morning” with “afternoon” starting at the 6th character.
Example 2:
Consider a phone number formatted as “(555) 123-4567”. If you want to remove the brackets and hyphen to format it as “5551234567”, employ the following:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")This series of SUBSTITUTE functions effectively removes the brackets and hyphen from the phone number.
These examples highlight the flexibility and utility of the REPLACE function in editing text within Excel and Google Sheets.
More information: https://support.microsoft.com/en-us/office/replace-replaceb-functions-8d799074-2425-4a8a-84bc-82472868878a