Yesterday, in the 30XL30D challenge, we used the INDEX function to return a value or reference, based on a row and/or column number.
REPLACE Function
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.
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,”-“)
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, watch this short Excel video tutorial.
_____________
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.