# How can I shorten this formula in Sheets?

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.

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"))
``````
• Perfect! Thank you for this thorough and thoughtful answer. Jun 26 at 1:03