Excel SUBSTITUTE function

Summary

The Excel SUBSTITUTE function replaces text in a given string by matching. It’s used to replace specific text in a string with another text. SUBSTITUTE is case-sensitive and provides precise control over replacements.
Syntax
				
					=SUBSTITUTE(text, old_text, new_text, [instance_num])
				
			
  • text: The original text string
  • old_text: The text to be replaced
  • new_text: The text to replace with
  • instance_num: [Optional] The specific occurrence of old_text to replace. If omitted, all instances are replaced
Return value
A new text string with the specified text replaced.

How to use

SUBSTITUTE is ideal for text manipulation tasks where specific instances of a text pattern need to be replaced. It’s commonly used in data cleaning, formatting, and preparation.

Examples

Simple SUBSTITUTE
Replacing Specific Text: To replace specific text within a string:
				
					=SUBSTITUTE(A1, "old", "new")
				
			
If A1 contains “oldtext”, this formula returns “newtext”.
SUBSTITUTE with Instance Number
Replacing a Specific Instance of Text: Using SUBSTITUTE to replace only the second instance:
				
					=SUBSTITUTE(B2, "apple", "orange", 2)
				
			
Replaces the second “apple” in B2 with “orange”.
SUBSTITUTE for Data Cleaning
Removing Unwanted Characters: Cleaning a string by removing specific characters:
				
					=SUBSTITUTE(C2, "-", "")
				
			
Removes all hyphens (“-“) from the text in C2.
SUBSTITUTE and LEN for String Modification
Replacing Based on Length: Conditional text replacement based on length:
				
					=SUBSTITUTE(D2, " ", "", IF(LEN(D2)>10, 1, 2))
				
			
Replaces the first or second space in D2 depending on its length.
SUBSTITUTE in Text Formatting
Formatting Text Output: Custom text formatting using SUBSTITUTE:
				
					=SUBSTITUTE("Hello World", "World", E2)
				
			
Replaces “World” in “Hello World” with the value in E2.

Additional Notes

  • SUBSTITUTE is case-sensitive. For case-insensitive replacements, consider using a combination of UPPER/LOWER functions with SUBSTITUTE.
  • Can be combined with other text functions like SEARCH or MID for complex manipulations.
  • Useful for replacing specific characters or text patterns in a standardized format.

Related Functions

Excel REPLACE function

The Excel REPLACE function substitutes part of a text string with a different text string, ideal for data correction and manipulation.

Content Navigation