A to Z of Excel Functions: The REPLACE Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the REPLACE function.

The REPLACE function

The REPLACE function replaces part of a text string, based upon the number of characters you specify, with a different text string.  This function is not available in all languages.  REPLACE is intended for use with languages that use the single-byte character set (SBCS), whereas REPLACEB is anticipated for use with languages that use the double-byte character set (DBCS).

REPLACE always counts each character, whether single-byte or double-byte, as one [1], no matter what the default language setting is.

It has the following syntax:

The REPLACE function has the following arguments:

• old_text: this is required and represents the text in which you wish to replace some or all of the characters
• start_num: this is also required.  This is the position of the character in old_text that you want to replace with new_text
• num_chars: this argument is also mandatory and denotes the number of characters in old_text that you want REPLACE to change for new_text
• new_text: again required, this is the text that will replace the characters in the old_text.

Please see my examples below: 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.