substitute

Previous chapterNext chapter Show allShow all    Hide allHide all

The function substitute (string, oldstring, newstring, instance) replaces a string (oldstring) in the text string (string) with a new one (newstring). Instance defines where oldstring should be replaced by newstring. With instance=0 all found strings are replaced. The function is case sensitive.

Syntax: =substitute(string, oldstring, newstring, instance)

Example Example

cell A1 = abcdef ABCDEF abcdef ABCDEF abcdef ABCDEF
cell A2 = XYZ
=substitute(A1,'ABCDEF',A2,0) is abcdef XYZ abcdef XYZ abcdef XYZ

In this example the whole character sequence "ABCDEF" in the entire String ABCDEF abcdef ABCDEF abcdef ABCDEF" is replaced by "XYZ!

=substitute(A1,'abcdef',A2,2) is abcdef ABCDEF XYZ ABCDEF abcdef ABCDEF

In this example, the whole character sequence 'abcdef' in the entire String ABCDEF abcdef ABCDEF abcdef ABCDEF is replaced by XYZ.