How to generate all combinations of letters in a word?

Using Google Sheets, how would I show all possible ways to type a word, for example:

``````word
Word
WOrd
WORd
WORD
wOrd
woRd
worD
``````

without any length word? The given word would go on Row A and the possible combinations would go in Row B.

• You are aware your list is missing eight combinations (`2^4 = 16`) and that this list will get very long with longer words?
Jun 19, 2019 at 15:13
• yes i'm aware i didnt add ALL the possible combinations, it was just to give an example. and NO i cant open excel 1st and then try and convert the VBA code so if you are able to tell me how to perform this in google sheets which im very new to that would be much appreciated. Jun 19, 2019 at 21:31

If you type your word by one character per cell with lowercase and then type it again with uppercase like this:

you could use this formula in F1 cell which will construct you with all possible states:

``````=ARRAYFORMULA(REGEXEXTRACT(
TRANSPOSE(SPLIT(REPT(CONCATENATE(TRANSPOSE(SPLIT(REPT(CONCATENATE(TRANSPOSE(SPLIT(REPT(CONCATENATE(
TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(C1:C, C1:C<>"")&CHAR(9),COUNTA(
TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))))),CHAR(9)))&CHAR(9)),COUNTA(D1:D)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(D1:D, D1:D<>"")&CHAR(9),COUNTA(TRANSPOSE(SPLIT(REPT(CONCATENATE(
TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(C1:C, C1:C<>"")&CHAR(9),COUNTA(
TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))))),CHAR(9)))))),CHAR(9)))&CHAR(9)),COUNTA(E1:E)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(E1:E, E1:E<>"")&CHAR(9),COUNTA(TRANSPOSE(SPLIT(REPT(CONCATENATE(
TRANSPOSE(SPLIT(REPT(CONCATENATE(TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(C1:C, C1:C<>"")&CHAR(9),COUNTA(
TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))))),CHAR(9)))&CHAR(9)),COUNTA(D1:D)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(D1:D, D1:D<>"")&CHAR(9),COUNTA(TRANSPOSE(SPLIT(REPT(CONCATENATE(
TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(C1:C, C1:C<>"")&CHAR(9),COUNTA(
TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&
TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))))),CHAR(9)))))),CHAR(9)))))),CHAR(9))),"^([^-]+)"))``````

note: for a word with less then 5 characters use single dashes (`-`) only within the first row:

• The epic 300K characters formula by simple `Ctrl+H`. stackoverflow.com/a/55070275/10789707 Thanks!