0

I'm using google sheets and the REGEXEXTRACT formula to extract all letters/numbers/hyphens in a range. In the examples below, I'm looking to remove the apostrophes.

'02 Blue Twisters

Blue Twisters '02

Blue Twisters 02-03

=ARRAYFORMULA(IF(DN3:DN="","",REGEXEXTRACT(DN3:DN,"\w+\w+\w+"))) =ARRAYFORMULA(IF(DN3:DN="","",REGEXEXTRACT(DN3:DN,"w.+"))) =ARRAYFORMULA(IF(DN3:DN="","",REGEXEXTRACT(DN3:DN,"w+"))) =ARRAYFORMULA(IF(DN3:DN="","",REGEXEXTRACT(DN3:DN,"[a-zA-Z0-9].+))) =ARRAYFORMULA(IF(DN3:DN="","",REGEXEXTRACT(DN3:DN,"[a-zA-Z0-9]+")))

\w+ seems work as intended HERE but they don't seem to do the trick in Google Sheets. Some remove that first apostrophe, but not interior ones. Some don't remove anything.

Any ideas as to what I may be missing

N.O.Davis
  • 501
  • 2
  • 10
  • 22
  • 2
    Although I'm not sure about the detail output you want, when you want to remove the apostrophes from the values, for example, how about using ``REGEXREPLACE()`` like ``=ARRAYFORMULA(IF(DN3:DN="","",REGEXREPLACE(A:A,"'","")))``? If I misunderstand your question, can you provide the sample output values you want? – Tanaike Dec 16 '18 at 00:32
  • Possible duplicate of [Why is REGEXEXTRACT returning a single value when it is expected to return an array of 1 row and multiple columns?](https://stackoverflow.com/questions/42826011/why-is-regexextract-returning-a-single-value-when-it-is-expected-to-return-an-ar) – Rubén Dec 16 '18 at 22:57

3 Answers3

1

Any ideas as to what I may be missing

SUBSTITUTE.

pnuts
  • 58,317
  • 11
  • 87
  • 139
0

Thanks for the help folks, I came across two solutions. They're a bit complex but work.

=ARRAYFORMULA(IF(ISNUMBER(SEARCH("(",H3:H))=TRUE,TRIM(REGEXREPLACE(H3:H,"\(|\)","")),IF(ISNUMBER(SEARCH("'",H3:H))=TRUE,TRIM(REGEXREPLACE(H3:H,"'","")),H3:H)))

=ARRAYFORMULA(IF(DN3:DN="","",TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DN3:DN,"("," "),")"," "),"'"," "),":"," "),";"," "),"!"," "),","," "),"."," "))))

N.O.Davis
  • 501
  • 2
  • 10
  • 22
0

extract all letters/numbers/hyphens in a range.

=REGEXREPLACE(A2, "[^A-Za-z0-9-]",)
TheMaster
  • 45,448
  • 6
  • 62
  • 85