RegEx: Extract all complete words before N characters

Question

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.

If you're using javascript, you can try ^.*\b(?<=^.{0,30}), check here to see the result. — Hao Wu
Excellent, thank you for your regex. Unfortunately, it will not work with REGEXEXTRACT() from Google Sheet, so I am still stuck with a super long formula for now (namely =if(LEN(REGEXEXTRACT(C$3,"^(?:\S+\s+\n?){1,"&counta(SPLIT(LEFT(C$3,30)," ",true, true))&"}"))<=30,REGEXEXTRACT(C$3,"^(?:\S+\s+\n?){1,"&counta(SPLIT(LEFT(C$3,30)," ",true, true))&"}"),REGEXEXTRACT(C$3,"^(?:\S+\s+\n?){1,"&counta(SPLIT(LEFT(C$3,30)," ",true, true))-1&"}"))) — denisq
My previous solution didn't take into account all the use cases, so I updated my answer. I hope this is helpful to you. — Iamblichus

Iamblichus Iamblichus · Accepted Answer · 2020-10-05T12:36:13

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):

Reference:

Custom Functions in Google Sheets

RegEx: Extract all complete words before N characters

1 Answers

Reference: