Related Function:

The REPLACE function replaces all or part of a text string with another string, based on the number of characters you specify. REPLACEB replaces all or part of a text string with another string, based on the number of bytes you specify.

Syntax

=REPLACE(old_text,start_num,num_chars,new_text)

=REPLACEB(old_text,start_num,num_bytes,new_text)

Note: The REPLACE function is similar to the SUBSTITUTE function, but the REPLACE function replaces text in a specified position of a supplied string, while the SUBSTITUTE function replaces one or more instances of a given text string.

Note: REPLACEB counts 2 bytes per character only when a double-byte character set (DBCS) language is set as the default language. Otherwise REPLACEB behaves the same as REPLACE, counting 1 byte per character.

Arguments

Argument Description
old_text The original text string, that you want to replace part of
start_num The position of the character in old_text that you want to replace with new_text
num_chars The number of characters in old_text that you want REPLACE to replace with new_text
num_bytes The number of bytes in old_text that you want REPLACEB to replace with new_text
new_text The text that will replace characters in old_text

Examples

  A B C D
1 Old text Formula Result Notes
2 http://www.bestfx.com =REPLACE(A2,8,4) http://bestfx.com/ Removes “www” by replacing with no characters
3 http://bestfx.com =REPLACE(A3,18,0,”/excel/”) http://bestfx.com/excel/ Adds the directory “excel” to the web address
4 http://bestfx.com/ID2556 =REPLACE(A4,21,2,52) http://bestfx.com/ID5256 Replace the 1st two characters of the article ID

Common Function Error(s)

Problem What went wrong
Using REPLACE with a date, time or numbers returns unexpected results REPLACE is designed for use with text strings and returns a text string.

  If you are not planning to use the date, time or number in further calculations, use the Text To Columns tool to convert date, time, and number values into text