Skip to main content

Replacement of string

This time, I will explain how to replace a specific string with another character string using an office script, and the points to note when it cannot be replaced well.

Method to use​

Use the replace method included in the String object.

I think that VBA used the Replace function to specify the target character string, the character string to be replaced, the replacement string, and three arguments.Execute the method.

Details of replace method
/**
 * @param searchValue Character string to be replaced
 * @param replaceValue Replaced character string
 */
replace(searchValue: string | RegExp, replaceValue: string): string;
Difference from VBA

It is important to note that the execution of this method will not change the original string.

The return value of this method is returned as a return value.

The original string is not changed
let target = 'I ate bread for breakfast';

let replaced = target.replace('bread', 'cooked rice');

console.log(target); // I ate bread for breakfast
console.log(replaced); // I ate rice for breakfast

If you want to delete a specific string, specify a blank ('') as the second argument.

You can also specify a regular expression in the first argument.

Sample code​

This is a sample code that specifies the target character string and stores replacement results in another variable.

Sample of replacement of string
function main(workbook: ExcelScript.Workbook) {
  /** Target character string */
  const target = 'My favorite fruit is Apple';

  /** Substitution string */
  const replaced = target.replace('Apple', 'Banana');

  console.log(replaced); // My favorite fruit is Banana
}