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.