1

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.

migrated from superuser.com Jun 19 at 19:42

This question came from our site for computer enthusiasts and power users.

  • 1
    You are aware your list is missing eight combinations (2^4 = 16) and that this list will get very long with longer words? – cybernetic.nomad Jun 19 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. – Wiiliam Bonney Jun 19 at 21:31
0

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

0

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))),"^([^-]+)"))

0


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

0


for 12 characters: spreadsheet

formula here

0


for 15 characters: spreadsheet

0

  • what about for a word longer than 5 characters ? is there a way to do this any make it work for say anything up to 12 characters ? it works great so thanks for that but it needs to be able to accommodate larger words. – Wiiliam Bonney Jun 19 at 23:06
  • how did you go user0 ? – Wiiliam Bonney Jun 20 at 5:45
  • WOW that's amazing thanks... do you know if there is any kind of website that can generate this kind of results without having to use a spreadsheet... im REALLY appreciate ll your help buy just wondering if there is an easier wow because that is some amazing but HUGE code – Wiiliam Bonney Jun 20 at 6:00
  • what if i wanted to be able use numbers or special characters like ! or _ for instance.. would that be possible ?? – Wiiliam Bonney Jun 20 at 7:56
  • not aware of any site. as for the characters, you can use any characters you wish. also, you may use more than 2 rows. if the spreadsheet stops due to huge formula I have may a solution with hidden helper columns – user0 Jun 20 at 9:56

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.