Extract part of a string

This article uses Office Scripts to explain how to extract part of a string, with sample code.

Difference from VBA

In VBA, you used Left, Mid, and Right to extract part of a string.

In Office Scripts, you mainly use the slice or substring method and specify a start position and an end position. Setting the start position to 0 works like Left, and passing a negative number to slice works like Right.

Methods to use

Using String.slice

Use the slice method of the String object.

Method details

/**
 * @param start The position where extraction starts
 * @param end The position where extraction ends (extraction stops just before this position)
 */
slice(start: number, end?: number): string;

You can pass negative numbers as arguments. A negative number means “the nth position from the end.”

Keep in mind that the start and end indexes start at 0 (VBA’s Mid starts at 1).

Also, the extracted characters go up to just before the index you specify as the second argument.

For example, slice(0, 3) extracts the first through third characters of the string.

If you omit the second argument, everything from the start position to the end of the string is extracted.

Tip

Because you can pass a negative number to extract from the end, this method should feel familiar if you’re used to VBA’s Right.

Sample using slice

function main(workbook: ExcelScript.Workbook) {
  /** Target string */
  const target = 'CatDogPig';

  console.log(target.slice(0, 3)); // Cat
  console.log(target.slice(3, 6)); // Dog
  console.log(target.slice(-3)); // Pig
}

Using String.substring

Use the substring method of the String object.

Method details

/**
 * @param start The position where extraction starts
 * @param end The position where extraction ends (extraction stops just before this position)
 */
substring(start: number, end?: number): string;

As with slice, the start and end indexes of this method start at 0.

Also like slice, the extracted characters go up to just before the index you specify as the second argument.

If you pass a negative number as an argument, it’s treated as 0. Also, if the start position is greater than the end position, the two arguments are swapped (substring(6, 3) gives the same result as substring(3, 6)).

If you omit the second argument, everything from the start position to the end of the string is extracted.

Sample code

Sample using substring

function main(workbook: ExcelScript.Workbook) {
  /** Target string */
  const target = 'CatDogPig';

  console.log(target.substring(0, 3)); // Cat
  console.log(target.substring(3, 6)); // Dog
  console.log(target.substring(6)); // Pig
}