1

In column A I have a long list of words (one per cell). My goal was to determine which words are made up of the same letters (e.g. Align, Lagging, and Ganglia, so repeated letters don't matter to me).

I made a formula to give each word a "score" by this system: An a is worth 1 point, a b is worth 2 points, a c is worth 4 points, and so on until z is worth 2^25 = 33,554,442 points. Words with the same score in this system must have the same letters.

My formula is long and cumbersome. It looks like: =if(isnumber(search("A",A1)),2^0,0)+if(isnumber(search("B",A1)),2^1,0)+... and so on all the way to a term that searches for Z.

I'm looking for help to make the formula more manageable. I thought having an array somewhere with letters in one column and their values in the adjacent column might help. But I hit a dead end on this.

1 Answer 1

1

Alright, after a few strokes of genius I got an answer. ( I am legitimately very excited for solving this one.) The use of binary is already pretty smart.

=SUM(UNIQUE(ArrayFormula(2^(CODE(SPLIT(REGEXREPLACE(UPPER(A1),"(\w)","$1 ")," "))-64)),TRUE))

Breakdown

  1. UPPER(A1) to standardize the letters for the CODE function.
  2. SPLIT(REGEXREPLACE(UPPER(A1),"(\w)","$1 ")," ") Splits each character into their own cell.
  3. We have all of that in an ArrayFormula so that we can raise 2 by each values.
  4. We then use UNIQUE to remove all duplicates. We also set the second argument to true, since SPLIT splits the values into columns.
  5. We then simply SUM it all up.

Notes

  1. I can't get this formula to work with an ARRAYFORMULA wrapping the entire thing, so drag it down or double click the fill handle.
  2. There is a function solution for splitting text by each character, but it's rather unruly. My method is shorter and better I think. An adjustment for ALL characters would be SPLIT(REGEXREPLACE(UPPER(A1),"(.)","$1😀"),"😀"). Though that would be pretty overkill when you're working with letters only. I'm not sure whether my method won't work on an edge case I didn't foresee, but it seems to work well with what I've tested it on.
  3. For some reason, the Google Docs Editors Help site has two different entries for UNIQUE, this one and this one. The former seems to be an older version that doesn't document the two optional arguments for the function.
  4. This binary system thing is great, but if you want an alternative solution where your unique value is a string that includes all the unique letters instead of an incomprehensible large number, then consider this function: (that doesn't play well with ARRAYFORMULA either.)
CONCATENATE(QUERY(UNIQUE(TRANSPOSE(SPLIT(REGEXREPLACE(UPPER(A1),"(\w)","$1 ")," "))), "SELECT * ORDER BY Col1"))
1
  • Perfect! Thank you for this thorough and thoughtful answer. Jun 26 at 1:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.