Excel: Replace Function

In Excel, the Replace function replaces a sequence of characters in a string with another set of characters.

The syntax for the Replace function is:

Replace( old_text, start, number_of_chars, new_text )

old_text is the original string value.

start is the position in old_text to begin replacing characters.

number_of_chars is the number of characters to replace in old_text.

new_text is the replacement set of characters

Applies To:

Excel 2007, Excel 2003, Excel XP, Excel 2000

For example:

Let's take a look at an example:

Based on the Excel spreadsheet above:

=Replace(A1, 1, 5, "Beta") would return "Betabet Soup"

=Replace(A2, 5, 2, "1234") would return "Tech1234TheNet"

=Replace("apples", 2, 5, "te") would return "ate"

VBA Code

The Replace function can also be used in VBA code. For example:

Dim LResult As String

LResult = Replace("alphabet", "a", "e")

In this example, the variable called LResult would now contain the value "elphebet".