REPLACE

REPLACE replaces items that match a specified value with another value. It can also be used to remove values from a formula or manipulate values and the way they appear in a Klip.

REPLACE( values, matching, with value)

values: specified data set.

matching: specific value(s) in the data set that will be replaced. 

with value: value to replace the matching element.

REPLACE values

REPLACE(SLICE(values), "matching", "with value")

  • values: set to the Country data in column A (A:A).
  • matching: set to the value, "Canada" (A2).
  • with value: set to the replacing value, "CAD".

Note: SLICE is used to remove the column header. 

REPLACE multiple values 

Note: This example is a continuation of the formula in the previous section.

The example below returns the column of data with both Canada and Germany replaced.

REPLACE(REPLACE(SLICE(values), "matching1", "with value1"), "matching2", "with value2")

  • values: set to the Country data in column A (A:A).
  • matching1: set to the value, "Canada" (A2).
  • with value1: set to the replacing value, "CAD".
  • matching2: set to the value, "Germany" (A10).
  • with value2: set set to the replacing value, "DE".

REPLACE and BLANK

The example below uses REPLACE to replace BLANK values with 0.

Hover over the image below to watch a short video.

Related links:

Data Manipulation functions