Skip to main content

Cut out character string

This time, we will use an office script to explain how to cut the string together with the sample code.

Difference from VBA

In VBA, Left, Mid, and Right were used to cut out character strings.

In the Office script, there are some functions equivalent to MID.

Method to use​

When using String.slice​

Use the slice method included in the String object.

Method details
/**
 * @param start First position of the cut characters
 * @param end The end position of the cut out character (until this number is cut out)
 */
slice(start: number, end?: number): string;

If the negative number can be specified in the argument, and if the negative number is specified, it represents the "N number from the back".

Please note that the index at the start / end position starts from 0.

Also, the characters to be cut are just before the index specified in the second argument.

For example, if you specify slice(0, 3), the first to his third of the string is cut out.

If the second argument is omitted, it will be eligible from the start position to the end of the character.

tip

Those who have learned VBA are easy to use because this function is similar.

Sample using slice method
function main(workbook: ExcelScript.Workbook) {
  /** Target character string */
  const target = 'AppleOrangeBanana';

  console.log(target.slice(0, 5)); // Apple
  console.log(target.slice(5, 11)); // Orange
  console.log(target.slice(-6)); // Banana
}

When using String.substring​

Use the substring method included in the String object.

Method details
/**
 * @param start First position of the cut characters
 * @param end The end position of the cut out character (until this number is cut out)
 */
substring(start: number, end?: number): string;

Similarly, this method starts and ends the index at the end position from 0.

Like slice, the cut out is just before the index specified as the second argument.

If the negative number is specified in the argument, it is considered 0.

If the second argument is omitted, it will be eligible from the start position to the end of the character.

Sample code​

Sample using substring method
function main(workbook: ExcelScript.Workbook) {
  /** Target character string */
  const target = 'AppleOrangeBanana';

  console.log(target.substring(0, 5)); // Apple
  console.log(target.substring(5, 11)); // Orange
  console.log(target.substring(11)); // banana
}