30 Excel Functions in 30 Days: 25 – REPLACE

Icon30DayYesterday, 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.

Replace00

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)

Replace01

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,”: “)

Replace02

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,”-“)

Replace03

Download the REPLACE Function File

To see the formulas used in today’s examples, you can download the REPLACE function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the REPLACE Video

To see a demonstration of the examples in the REPLACE function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Change Text with Excel REPLACE Function

_____________

You may also like...

1 Response

  1. Jason Morin says:

    Using REPLACE to insert characters is useful. You can use it to convert text strings to actual dates. I looked up an old post of mine where the user download text in the format “mmm yy” and needed them as dates. I used:

    =REPLACE(TRIM(A1),5,,”20?)*1

    I assumed all dates were no older than 2000.

Leave a Reply

Your email address will not be published. Required fields are marked *