Excel REPLACE function

Summary

The Excel REPLACE function replaces part of a text string with a different text string. This function is particularly useful for modifying specific portions of strings based on their character position.
Syntax
				
					=REPLACE(old_text, start_num, num_chars, new_text)
				
			
  • old_text: The original text string
  • start_num: The position of the first character in the old_text that you want to replace
  • num_chars: The number of characters in old_text that you want to replace
  • new_text: The text that will replace characters in old_text
Return value
A new text string resulting from the replacement.

How to use

REPLACE is used for text manipulation, especially when you need to change specific parts of a string based on their character positions. It’s useful in data cleaning, formatting, and preparation.

Examples

Simple REPLACE
Replacing Specific Characters in a String: To replace part of a string:
				
					=REPLACE(A1, 4, 3, "XYZ")
				
			
If A1 contains “HelloWorld”, this formula replaces “loW” with “XYZ”, resulting in “HelXYZorld”.
REPLACE with Cell References
Dynamic Text Replacement: Using REPLACE based on cell references:
				
					=REPLACE(B2, 2, 5, C2)
				
			
Replaces 5 characters in B2, starting from the 2nd character, with the text in C2.
REPLACE for Formatting Codes
Modifying Parts of Codes or IDs: To change a specific part of a code or ID:
				
					=REPLACE(D2, 5, 2, "AB")
				
			
If D2 contains “1234-5678”, it changes to “1234-AB78”.
REPLACE in Data Cleaning
Correcting Specific Text Segments: Using REPLACE for targeted text corrections:
				
					=REPLACE(E2, 1, 3, "New")
				
			
Replaces the first 3 characters in E2 with “New”.
REPLACE for Custom Formatting
Applying Custom Formatting to Strings: Creating a custom text format by replacing specific characters:
				
					=REPLACE("123456789", 4, 0, "-")
				
			
Inserts a hyphen after the 3rd character, resulting in “123-456789”.

Additional Notes

  • REPLACE is case-sensitive.
  • For replacing text based on content rather than position, use the SUBSTITUTE function.
  • Useful for formatting strings that follow a specific structure, like phone numbers or serial codes.

Related Functions

Excel SUBSTITUTE function

The Excel SUBSTITUTE function replaces text in a string, ideal for altering data or removing unwanted characters.

Excel FIND function

The Excel FIND function locates one text string within another, returning the start position, crucial for text analysis.

Content Navigation