To replace part of a text string with new substrings based on position
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”.
=REPLACE(old_text, start_num, num_chars, new_text)
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
Newly updated text
- 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.