Excel Function: REPLACE

Purpose

The REPLACE function swaps a section of a text value with another text value. Its output is always a value of type text. The location of the section is defined with the start_num and num_chars arguments. The start_num argument defines the position of the leftmost character of the section to be swapped, and the num_chars argument defines the total number of characters the section contains. Use the SUBSTITUTE function if it isn’t possible to define the location of the section.

Invocations of the REPLACE function take the form:

REPLACE(old_text,start_num,num_chars,new_text)

Example

Consider a cell containing the formula:

=REPLACE("Gray car", 2, 3, "reen")

The formula instructs the REPLACE function to swap the section of the text value “Gray car“, 3 characters in length and starting at character 2, with the text value “reen“. Characters 2 to 4 inclusive of “Gray car” form the text value “ray” and the REPLACE function returns “Green car“. The process is illustrated below:

Arguments

Argument	Argument type	Description
old_text	Mandatory	A text value containing a section that will be swapped for the value of the new_text argument.
start_num	Mandatory	The position of the leftmost character of a section of old_text to be swapped with new_text.
num_chars	Mandatory	The number of characters in a section of old_text to be swapped with new_text.
new_text	Mandatory	The text value that will replace the section of old_text defined by start_num and num_chars.

Related Functions

Function	Example	Purpose
SUBSTITUTE	SUBSTITUTE(text,old_text,new_text)	Its purpose is identical to that of the REPLACE function: the SUBSTITUTE function swaps a section of a text value with another text value. SUBSTITUTE differs from REPLACE in that: only the sequence of characters to be swapped needs to passed to it, not its location. if a sequence of characters occurs ≥ 1 times in a text value, it can swap each of them.

Error Conditions

Error value	Condition type	Condition
#VALUE!	Bad argument	A value ≤ 0 was supplied to the start_num argument.
#VALUE!	Bad argument	A value ≤ -1 was supplied to the num_chars argument.
#VALUE!	Bad argument	A value of type currency, geography or stock was supplied to the old_text argument.

Examples

Example	Formula	Result	Description
1	=REPLACE("ABC",3,1,"DE")	ABDE	Swap the third character of the text value “ABC”, with the text value “DE”. The section of “ABC” to be swapped: Is 1 character in length. Begins at character 3. The REPLACE function: Swaps “C” with “DE”. Returns the text value “ABDE”.
2	=REPLACE("ABC",4,1,"DE")	ABCDE	Swap the fourth character of the text value “ABC”, with the text value “DE”. The section of “ABC” to be swapped: Is 1 character in length. Begins at character 4. The REPLACE function: Recognises “ABC” has no fourth character. Appends “ABC” with “DE”. Returns the text value “ABCDE”.
3	=REPLACE("ABC",5,1,"DE")	ABCDE	Swap the fifth character of the text value “ABC”, with the text value “DE”. The section of “ABC” to be swapped: Is 1 character in length. Begins at character 5. The REPLACE function: Recognises “ABC” has no fifth character. Appends “ABC” with “DE”. Returns the text value “ABCDE”.
4	=REPLACE(REPLACE("AB",1,1,"C"),2,1,"D")	CD	Subject the value “AB” to the REPLACE function twice. The first invocation of the REPLACE function: Operates on the text value “AB”. Swaps “A” with “C”. Returns the value “CB”. The second invocation of the REPLACE function: Operates on the text value “CB”. Swaps “B” with “D” Returns the value “CD”.

Examples: Errors

Example	Formula	Result	Description
1	=REPLACE("ABC",0,1,"DE")	#VALUE!	Swap the character at position 0 of the text value “ABC”, with the text value “DE”. Because no character in a text value is in position 0, #VALUE! is returned.
2	=REPLACE("ABC",1,-1,"DE")	#VALUE!	Swap a section of “ABC” -1 characters in length with “DE”. Because a section can only be ≥ 1 character in length, #VALUE is returned.