REPLACE function

REPLACE( values, matching, with value)

REPLACE replaces any value found in values that matches any value found in matching with the value found in with_value or blank if no with_value is provided.

How to use the REPLACE function

The REPLACE function is used to specify a value that you want to replace and the value you want to replace it with. The REPLACE function can be used to remove values from a formula, or manipulate them so they appear a certain way in your Klip.  The REPLACE consists of 3 elements: values, matching, and with value. The values element specifies the data set to query, the matching element sets the parameters of the query, and the with value element specifies value to replace the matching element with. If the with value element is left blank, the replace will remove all values from the matching element from the Klip.

To use the REPLACE function to replace a single value

Note: These procedures assume you are in the Klip Editor as the result of building a Klip or editing a Klip.

  1. Select a component from the component tree (Column, Primary Value, etc).
  2. Click the Start formula link in the formula editor.
  3. Click the Insert Function button on the formula editing toolbar.
  4. Select Data Manipulation>REPLACE from the Function's menu.

  1. Click the values link in the REPLACE function.
  2. Select a values(s) to query in the function.
  3. Click the matching link in the REPLACE function.
  4. Select the value you want to replace.
  5. Click the with value link in the REPLACE function.
  6. Click the Insert literal string or number button on the formula editing toolbar.
  7. Type a value that you want replace the matching element of the function.
  8. Click the Apply button.

The value in the matching element is replaced by the with value element. If no with value element is specified, the value will be replaced by a blank.


To use the REPLACE function to replace multiple values 

Note: These procedures assume you are in the Klip Editor as the result of building a Klip or editing a Klip. 

  1. Select a component from the component tree (Column, Primary Value, etc).
  2. Click the Start formula link in the formula editor.
  3. Click the Insert Function button on the formula editing toolbar.
  4. Select Data Manipulation>REPLACE from the Function's menu.

  1. Click the values link in the REPLACE function.
  2. Click the Insert Function button on the formula editing toolbar.
  3. Select Data Manipulation>REPLACE from the Function's menu.

Note: This will allow you to replace two values in your data source. To replace more values, repeat steps 5-7 as many times as you have values to replace.

  1. Click the values link in the nested REPLACE function.
  2. Select a range of values to query.
Tip! Use the SLICE function to clean up the data before using it in a Klip.
  1. Click the matching link in the nested REPLACE function.
  2. Select the value you want to replace.
  3. Click the with value link in the nested REPLACE function.
  4. Click the Insert literal string or number button on the formula editing toolbar.
  5. Type a value that you want replace the matching element of the function.
  6. Click the Apply button.
  7. Click the matching link for the first REPLACE function.
  8. Select the value you want to replace.
  9. Click the with value link in the first REPLACE function.
  10. Click the Insert literal string or number button on the formula editing toolbar.
  11. Type a value that you want replace the matching element of the function.
  12. Click the Apply button.

The value in the matching element is replaced by the with value element. If no with value element is specified, the value will be replaced by a blank.


Examples of the REPLACE function

The REPLACE function is useful because it allows you to specify any number of values you want to replace in your data source. This may be as simple as providing abbreviation for country names (as in the images above). The following examples show different ways you can use the REPLACE function.

Using the REPLACE function to insert images

You can use the REPLACE function to specify a value for replacement, and then replace it with an image URL. This is a unique way to use this feature and can enhance the visual quality of your Klips. Remember to format the Klip component as an Image URL, and specify appropriate dimensions for the image. When image is clicked from the dashboard page, users will be provided with the full-sized image.


Related links:

Data Manipulation functions

Functions

  1. Introduction: Using formulas
  2. Data Manipulation functions
  3. Logic functions
  4. Math functions
  5. SELECT function
  6. Text Functions
  7. Date and Time Functions
  8. Statistics Functions
  9. ABS function
  10. AND function
  11. ARRAY function
  12. AVERAGE function
  13. BETWEEN function
  14. BLANK function
  15. CAPITALIZE function
  16. CEILING function
  17. CONCAT function
  18. CONTAINS function
  19. COUNT function
  20. COUNTALL function
  21. COUNTBLANK function
  22. COUNTDISTINCT function
  23. COUNTIF function
  24. COUNT_DAYS function
  25. COUNTNUMERIC function
  26. CUMULATIVE function
  27. CUMULATIVE_DIFFERENCE function
  28. DATASOURCE function
  29. DATE and DATEVALUE functions
  30. Using Date and Time Formats
  31. DATERANGE function
  32. DATE_ADD function
  33. DATE_CLOSEST function
  34. DATE_CONVERT function
  35. DATE_ENDOF function
  36. DATE_IN function
  37. DATE_SET function
  38. DATE_STARTOF function
  39. DATE_UNITVALUE function
  40. FLOOR function
  41. ERF function
  42. FIRST function
  43. ERFC function
  44. GROUP and GROUPBY functions
  45. IF function
  46. IN function
  47. INDEXOF function
  48. JOIN function
  49. LAST function
  50. LASTINDEXOF function
  51. LEFT function
  52. LOOKUP function
  53. LOWER function
  54. MAP function
  55. MAX function
  56. MIN function
  57. MEDIAN function
  58. MOD function
  59. MODE function
  60. Moving Average (Cumulative)
  61. Moving Average (Exponential)
  62. Moving Average (Simple)
  63. NORMSDIST function
  64. NOT function
  65. NOW function
  66. NUMBERFORMAT function
  67. OR function
  68. PADVALUES function
  69. POWER function
  70. RANK function
  71. REMOVE_EMOJI function
  72. REPEAT function
  73. REPLACE function
  74. REVERSE function
  75. RIGHT function
  76. ROUND function
  77. SET function
  78. SLICE function
  79. SLOPE function
  80. SORT function
  81. SPLICE function
  82. STANDARDIZE function
  83. STDEV function
  84. STDEVP function
  85. SUBSTITUTE function
  86. SUBSTITUTE_REGEX function
  87. SUBSTRING function
  88. SUM function
  89. SUMIF function
  90. SWITCH function
  91. TEXT_REVERSE function
  92. TODAY function
  93. TRIM function
  94. TRUNCATE function
  95. UPPER function
  96. URLDECODE function
  97. URLENCODE function
  98. VARIANCE function
  99. VARIANCEP function
  100. YESTERDAY function
  101. ZTEST function

Feedback and Knowledge Base