Create Regex search string with Regex in google sheet

Question

I want to know if there is a way that I can use regex to create a regex search string for anagrams.

Related to this post: Regex - find anagrams and sub-anagrams

I've created this spreadsheet with the word database

The regex to search for anagrams of acne is : "^(?!.*a.*a)(?!.*c.*c)(?!.*n.*n)(?!.*e.*e)[acne]*$" (from the aforementioned stackoverflow question).

I would like to use regexreplace to get from "acne" (or bacon or whatever string of letters to get to) "^(?!.*<letter1>.*<letter1>)<repeat for each letter>[<letterstring>]*$"?

If tried regexreplace("acne",".",".*") but that returns .*.*.*.* obviously. And what I was looking for is .*a.*c.*n.*e to then from there extrapolate further to get to "^(?!.*a.*a)(?!.*c.*c)(?!.*n.*n)(?!.*e.*e)[acne]*$"

Obviously I'd love it solve the multiletter dilemmas too: ^(?!.*([agoid]).*\1)(?!(.*m){3})[magoid]*$ but not sure how easy this will be with repeat letters.

Can you share the spreadsheet please? – Robin Gertenbach Jan 30 '17 at 15:06 — Robin Gertenbach, Jan 30 '17 at 15:06
Sorry I thought I did. It's shared now. – Reenen Jan 30 '17 at 15:12 — Reenen, Jan 30 '17 at 15:12

Robin Gertenbach · Accepted Answer · 2017-01-30T15:34:55.227

Do you mean like this?

="^(?!.*" & 
  JOIN(")(?!.*", 
       ArrayFormula(SPLIT(
         REGEXREPLACE(A1, 
                      "(\w)", "$1~"), 
         "~") & ".*" & 
       SPLIT(
         REGEXREPLACE(A1, 
                      "(\w)", "$1~"), 
         "~"))) & 
 ")[" & A1 & "]*$"

F1: REGEXREPLACE(A1, "(\w)", "$1~"), puts a tilde as a separator between every letter

F2: SPLIT(<F1>, "~") splits the word into an array

F3: ARRAYFORMULA(<F2> & ".*" & <F2>) creates an array of <letter_i>.*<letter_i>

F4: JOIN(")(?!.*", <F3>) joins the array together with the needed separators

"^(?!.*" & <F4> & ")[" & A1 & "]*$" appends the beginning and the end.

Edit

At that point you might as well check

= JOIN(, SORT(TRANSPOSE(SPLIT(REGEXREPLACE(A1, "(\w)", "$1~"), "~")))) = 
  JOIN(, SORT(TRANSPOSE(SPLIT(REGEXREPLACE(B1, "(\w)", "$1~"), "~"))))

Wow dude if I can give you 1000 upvotes I would! This is amazing. And not just the regex, the sheets functions too. I must read up more on Regex. — Reenen, Jan 31 '17 at 04:30

Create Regex search string with Regex in google sheet

1 Answers1

Edit