30 Excel Functions in 30 Days: 25 – REPLACE

Yesterday, in the 30XL30D challenge, we used the INDEX function to return a value or reference, based on a row and/or column number.
For day 25 in the challenge, we’ll examine the REPLACE function, which is in the Text category. It replaces a specified number of characters in a text string, with new text.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the REPLACE information and examples, and if you have other tips or examples, please share them in the comments.

Function 25: REPLACE

The REPLACE function replaces characters within text, based on the number of characters, and starting position, specified.

How Could You Use REPLACE?

The REPLACE function can replace characters in a text string, such as:

change area code in phone number

Replace first space with colon and space

Use nested REPLACE to insert hyphens

REPLACE Syntax

The REPLACE function has the following syntax:

REPLACE(old_text,start_num,num_chars,new_text)

old_text is the text string in which characters will be replaced.

start_num is the position of the old characters

num_chars is the number of old characters that will be replaced

new_text is the text that will replace the original text

REPLACE Traps

The REPLACE function replaces a specified number of characters at the indicated starting position. To replace a specific text string, anywhere in the original text, you can use the SUBSTITUTE function, which we’ll see later in the challenge.

Example 1: Change area code in phone number

With the REPLACE function, you can change the first three digits in a phone number, when a new area code is introduced. In this example, the new area code is entered in column C, and the revised phone numbers are shown in column D.=REPLACE(B3,1,3,C3)

Example 2: Replace first space with colon and space

To identify the starting position for the REPLACE function, you can use the FIND function, to locate a specific text string or character. In this example, we want to replace the first space character with a colon and space character.=REPLACE(B3,FIND(” “,B3,1),1,”: “)

Example 3: Use nested REPLACE to insert hyphens

The REPLACE function can be nested, so multiple replacements are made in the old text string. In this example, the list of phone numbers needs to have hyphens inserted, after the first 3 numbers, and after the second 3 numbers. By using zero as the number of characters to replace, none of the numbers will be removed, and hyphens will be inserted.=REPLACE(REPLACE(B3,4,0,”-“),8,0,”-“)