Replace and substitute functions

To use these functions, choose Calc > Calculator.

The Replace function replaces a substring of text within a string of text. The Substitute function replaces existing text with new text and lets you specify which occurrence of the original text you want to replace if the text occurs more than once in a single entry.

Syntax

REPLACE(old_text,start_num,num_chars,new_text)

For old_text, specify the original text or column of text values. For start_num, specify the position of the first character to replace. For num_char, specify how many characters to replace. For new_text, enter the new text that you want to replace the original text with.

SUBSTITUTE(text,old_text,new_text,[instance_num])

For text, specify the text or column of text. For old_text, specify the text that you want to replace. For new_ text, specify the new text you want to substitute. For the last (optional) argument, instance_number, you can specify which occurrence of the old text you want to replace, if the original text occurs more than once.

Example

Column	Calculator expression	Result
C1 contains "Joan Smith"	REPLACE(C1,1,4,"Ted")	Ted Smith
C1 contains "600 Pine Lane"	SUBSTITUTE(C1,"0","2",1)	620 Pine Lane