2

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.

Improve this question
2
  • 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, 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.
    – Wiiliam Bonney
    Jun 19, 2019 at 21:31

1 Answer 1

Reset to default
1

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

Improve this answer
9
  • 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, 2019 at 23:06
  • how did you go user0 ?
    – Wiiliam Bonney
    Jun 20, 2019 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, 2019 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, 2019 at 7:56
  • 1
    The epic 300K characters formula by simple Ctrl+H. stackoverflow.com/a/55070275/10789707 Thanks!
    – Lod
    Feb 16 at 2:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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