Using Excel REPLACE and SUBSTITUTE functions - formula examples

This tutorial demonstrates the Excel REPLACE and SUBSTITUTE functions with practical examples. Learn how to use REPLACE with text, numbers, and dates, and how to nest multiple REPLACE or SUBSTITUTE functions within a single formula.

Last week, we explored the FIND and SEARCH functions. Today, we delve into two more functions for replacing text within a cell: REPLACE (based on position) and SUBSTITUTE (based on content).

Excel REPLACE Function

The REPLACE function swaps characters in a text string.

REPLACE(old_text, start_num, num_chars, new_text)

• old_text: The original text (or cell reference).
• start_num: The starting position of the replacement.
• num_chars: The number of characters to replace.
• new_text: The replacement text.

Example: Changing "sun" to "son": =REPLACE("sun", 2, 1, "o") Or, using a cell reference: =REPLACE(A2, 2, 1, "o")

Using REPLACE with Numbers and Dates

REPLACE primarily works with text. While it can replace numeric characters within a text string, the result is always text (left-aligned, unlike numbers).

Example: =REPLACE(A2, 7, 4, "2016")

With dates, you must first convert the date to text using the TEXT function to avoid errors.

Example: =REPLACE(TEXT(A2, "dd-mmm-yy"), 4, 3, "Nov")

Nested REPLACE Functions

Multiple replacements can be achieved by nesting REPLACE functions.

Example: Adding hyphens to a phone number: =REPLACE(REPLACE(A2,4,0,"-"),8,0,"-")

Replacing Strings at Variable Positions

Use the FIND function to locate the starting position when the text to be replaced is at a different position in each cell.

Example: Replacing "ABC" with "BCA" in email addresses: =IFERROR(REPLACE(A2, FIND("@abc",A2), 4, "@bca"),A2) (IFERROR handles cases where "@abc" isn't found).

Excel SUBSTITUTE Function

SUBSTITUTE replaces all or specific instances of a text string.

SUBSTITUTE(text, old_text, new_text, [instance_num])

• text: The original text.
• old_text: The text to replace.
• new_text: The replacement text.
• instance_num (optional): The instance of old_text to replace (if omitted, all instances are replaced).

Example: Replacing the first "1" with "2": =SUBSTITUTE(A2, "1", "2", 1)

Nested SUBSTITUTE Functions

Multiple substitutions can be done by nesting SUBSTITUTE functions.

Example: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"PR","Project "),"ML","Milestone "),"T","Task ")

REPLACE vs. SUBSTITUTE

• SUBSTITUTE: Replaces based on content, all or specific instances.
• REPLACE: Replaces based on position and number of characters.

This concludes our guide. Download the practice workbook for further exploration.

The above is the detailed content of Using Excel REPLACE and SUBSTITUTE functions - formula examples. For more information, please follow other related articles on the PHP Chinese website!