CODEWITHSUNDEEP
regexgoogle-sheets
denisq
denisq

Reputation: 460

RegEx: Extract all complete words before N characters

I am looking for a short way to capture the first part of a sentence (in complete words) until the 30th character.

Example: "Lorem ipsum dolor sit amet, consectetur adipiscing elit" (55 chars) --> "Lorem ipsum dolor sit amet," (28 chars)

I will use this in Google Sheet, so the solution can be a combination of RegEx and formulas, but it needs to be super concise because it will be applied over a big range of data.

Upvotes: 0

Views: 300

Answers (1)

Iamblichus
Iamblichus

Reputation: 19319

If you want a very concise formula, you could use an Apps Script Custom Function.

First, open a bound script by selecting Tools > Script editor, and copy the following function to the script (check inline comments):

function FIRST_WORDS(input, number) {
  if (input.length <= number) return input;
  input = input.substring(0, number + 1);
  const lastSpace = input.lastIndexOf(" ");
  if (lastSpace === -1) return "";
  return input.slice(0, lastSpace);
}

Once it is defined, you can use this function the same you would any sheets built-in function. You just need to specify the source range and the number of characters (in this case, 30):

enter image description here

Reference:

Upvotes: 1

Related Questions