CODEWITHSUNDEEP
regexgoogle-apps-scriptgoogle-sheets
Umar Arshad
Umar Arshad

Reputation: 123

How to extract first 20 plus words from text string in google sheet

I want to extract first 22 words from a text string in column A in google sheet.

here is a google sheet Google sheet url

I used following formula on A2 in above google sheet

=regexextract(A2,"[\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]*")

But B2 cell is giving following error

Function REGEXEXTRACT parameter 2 value "[\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]* [\w]*" does not match text of Function REGEXEXTRACT parameter 1 value "Immediate Hiring ! ! Position Business Development Manager Location Dubai Industry Contracting Req Must have minimum 10+ years of UAE sales management Gender Male Preferred Nationality Pakistani Salary AED 10000-15000Send CV to  with Sub of email as " BDM " . NB This is one of the clients requirements. The above details are as specified by the".

How to extract first 22 words from each cell in A column and show in column B respective cell

any code based solution or any formula based solution is welcomed.

Upvotes: 2

Views: 1283

Answers (3)

Ryszard Czech
Ryszard Czech

Reputation: 18621

Use

=regexextract(A2,"\w+(?:\W+\w+){21}")

See regex proof.

EXPLANATION

--------------------------------------------------------------------------------
  \w+                      word characters (a-z, A-Z, 0-9, _) (1 or
                           more times (matching the most amount
                           possible))
--------------------------------------------------------------------------------
  (?:                      group, but do not capture (21 times):
--------------------------------------------------------------------------------
    \W+                      non-word characters (all but a-z, A-Z, 0-
                             9, _) (1 or more times (matching the
                             most amount possible))
--------------------------------------------------------------------------------
    \w+                      word characters (a-z, A-Z, 0-9, _) (1 or
                             more times (matching the most amount
                             possible))
--------------------------------------------------------------------------------
  ){21}                    end of grouping

Upvotes: 6

Yuri Khristich
Yuri Khristich

Reputation: 14537

If you don't mind to use a custom function here you go:

function GET22WORDS(cell) {
  return cell.match(/\w{3,}/g).slice(0,22).join('\n');
}

enter image description here

Upvotes: 3

Mike Steelson
Mike Steelson

Reputation: 15328

Try

=QUERY(iferror(flatten(arrayformula(regexextract((split(A2," ")&" "),"[\w]{3,} ")))),"select * where Col1 is not null limit 22")

I limit to signicant words, greater than or equal to 3 characters. Or

=TEXTJOIN(char(10),,QUERY(iferror(flatten(arrayformula(regexextract((split(A1," ")&" "),"[\w]{3,} ")))),"select * where Col1 is not null limit 20"))

enter image description here

Upvotes: 2

Related Questions