Rational
To replace part of a text string with new substrings based on position
Description
The Excel REPLACE function switches part of a text string, based on the specified starting position and number of characters, with a new text string. For example, =REPLACE(“Michael Jackson”,9,6,”Jordan”) returns “Michael Jordan”.
Syntax
=REPLACE(old_text, start_num, num_chars, new_text)
Parameters
old_text – The text to be replaced
start_num – The first character in old_text to start replacing with new_text
num_chars – The number characters to replace with new_text
new_text – The text to replace old_text characters
Return Value
Newly updated text
Key Notes
- The SUBSTITUTE function can also easily replace old text with a new text.
- To detect the text to replace, use FIND or SEARCH function in case of large data set or advance requirements.
- Space (“ ”) is counted as one of the characters while replacing the new text.
- The Excel may return an error in case of replacing date, time, or a number as the function is built for using text strings and returns a text string.
- You can easily replace a part of the date with another date selecting year, month, or day position in the given format.
Keep Reading Similar Functions:
CONCATENATE
The CONCATENATE function display text by merging multiple text strings. I.e., combine separate texts, =CONCATENATE(“Indian “, “Ocean”) returns “Indian Ocean”.
CONCAT
The CONCAT function display text by combining multiple ranges, array and/or strings. I.e., =CONCAT(“Mike “, “Tyson”) returns “Mike Tyson”, note space after “Mike ”.
SUBSTITUTE
The SUBSTITUTE function replace the specific text in a text string that occurs in a specific location. i.e, =SUBSTITUTE(“Butter”,”t”,”l”,2) return “Butler”.
