REPLACE

Replaces part of a text string with a different text string.

Syntax:

REPLACE(originaltext, startposition, length, newtext)

in originaltext, removes length characters beginning at character startposition, replaces them with newtext, and returns the result.

startposition and length must be 1 or more.

Example:

REPLACE("mouse", 2, 3, "ic")

returns mice. Beginning at character position 2, 3 characters (ous) are removed and replaced by ic.

Application:

Correcting a Product Code Format

Imagine you have a list of product codes in your inventory, but there's a formatting issue. All the codes should start with "PROD-", but due to a data entry error, some were entered with "PDC-". You need to quickly and automatically correct all the "PDC-" prefixes to "PROD-".

Table:

	Product Code (Original)	Desired Product Code	Corrected Product Code
	A	B	C
1	PDC-A123-456	PROD-A123-456	PROD-A123-456
2	PROD-B987-654	PROD-B987-654	PROD-B987-654
3	PDC-C321-789	PROD-C321-789	PROD-C321-789
4	PROD-D654-321	PROD-D654-321	PROD-D654-321
5	PDC-E987-123	PROD-E987-123	PROD-E987-123

The Formula in Cell B2:

You would use the following formula in the "Corrected Product Code" column. This formula first checks if the original code needs to be changed, and only then applies the REPLACE function.

IF(LEFT(A2,3)="PDC", REPLACE(A2, 1, 3, "PROD"), A2)

Explanation of the Formula:

This is a classic IF statement with three parts: IF(logical_test, value_if_true, value_if_false).

logical_test: LEFT(A2,3)="PDC"
- LEFT(A2,3) extracts the first three characters from the text in cell A2.
- The formula then checks if these three characters are equal to "PDC".
- This is the condition that determines whether a change is needed.
value_if_true: REPLACE(A2, 1, 3, "PROD")
- If the logical_test is true (i.e., the code starts with "PDC"), this part of the formula is executed.
- The REPLACE function then works as described in the previous example: it takes the text from cell A2, starts at the 1st position, replaces 3 characters, and inserts "PROD".
value_if_false: A2
- If the logical_test is false (i.e., the code does not start with "PDC", which includes the correct "PROD-" codes), this part of the formula is executed.
- It simply returns the original value from cell A2 unchanged.