Someone asked about how to find all the permutations of text and I thought it was a perfect example to show you how to use Spintax add-on. Free for everyone.
→ Get Spintax
→ Get Spintax
Academy ↓
Hey there stranger!
Every Possible Permutation - Solved with Spintax
About this Tutorial
Sheet Resources
Featured Formulas
Video Transcript
0:00 So I had someone recently ask me how to create every possible permutation where we know some base Text and then we have a hyphen and then we have some number of permutations sort of variety We want to essentially we want to take a base X Y or Z and add whatever permutations are in B column and the result
0:19 is here, C. So we can absolutely type this out, right? We can, we know if we have X, 1, 2, 3, we can do X, X and in this case they wanted a hyphen and then that number, that 1, 2, or 3.
0:31 So we can do X, 1, X hyphen 2, X hyphen 3. Absolutely we can type that by hand, right? But we don't want to do that by hand, we want to do this automatically, right?
0:41 Automatically get every possible permutation including the base one. So uhm here's the answer, this, this is going to be the entire formula.
0:48 We have a special SPINALL formula in here, we're going to use IF is blank, we're going to use JOIN, we're going to use SPLIT, uhm and SPIN or SPINALL is actually a custom function with a Google Sheet add-on that I myself created, it is absolutely free.
1:04 So if you're watching this anywhere on BetterSheets or on YouTube, check it out, you can go to add-ons, get add-ons and we're going to need to get this absolutely free Google Sheet add-on in order to complete it in this particular way.
1:15 There may be some other ways, but we're going to type in SPINTAX and if you type in just SPINTAX, you'll find it, SPINTAX Content Spinner, install it, it's absolutely free, uhm, and I just added actually some more stuff to it, but I'll get into that later, because right now we just want to spin this,
1:32 and what SPINTAX allows us to do is essentially take any kind of, uhm, this, not any kind, but a certain type of syntax, which we're going to use brackets, and then some x, and then a pipe to separate the options, let's say x.
1:52 X, y, z in this case. And now we have this bracket x, y, z. Well, say we want to do hello space and then x, hello y, hello z, and we want to separate those.
2:04 So here's what it would look like. So we would have hello and then we would do equals spin all, here's the output of that e3 here, e3.
2:19 And what it's going to do is it's going to put out hello z, hello y, hello x, right? In this case we can also add hello andrew, carl, zanzibar, right?
2:33 And now it's going to show hello zanzibar, hello carl, hello andrew, all of the permutations of it. So we need to, in this particular case, the person who was asking had these permutations 1, 2, 3 in these comments and we have to separate that and that's where we get, we're going to use split, we're
2:48 going to use join to combine them but we'll do a new result here and I will do it all from the ground up.
2:54 So first off we need to join by the pipe. So that's the, this up pipe thing. Put that in quotes.
3:07 And what are we going to join? Well we're going to join a split of this 1, 2, 3, 4. We're going to split, the delimiter is going to be a comma.
3:15 We are going to split by each. And now we see in our array, in our result, it's 1, 2, 3.
3:22 And now we need to concatenate. Actually we don't need concatenate here, but we can. We just use the ampersand and we can put in quotes the first curly bracket, the ampersand, another ampersand, and in quotes the end curly bracket.
3:39 And now we get the spin tax that we need. But we also need the base. So we need to do a 3 ampersand, and again, we're using ampersand here, but we can also use concatenate if we want to concatenate all these things together.
3:55 And now we have x3. But we want specifically, in quotes, a 3 a hyphen. And so we'll do that with ampersand as well, so we'll have x hyphen.
4:05 And now, this is our spin tax that we're going to now use, spin all around this, but we're going to get all of these in different rows, right?
4:18 So what we want to do is join them with a delimiter of a comma to separate, or whatever delimiter you want between these permutations.
4:28 What are we going to separate? We're going to have all these values from this spin all. There. Now we have them all in one place, but we're missing the base one.
4:37 And so we need to add the base. A three ampersand, and then a comma, and then a join. And then another ampersand, and then the join.
4:47 And so now we have, of The thing is, if we copy and paste this down, we'll see we have now the permutations of this y, this z.
4:56 But look at this. We have hashtag value. Why do we have hashtag value? It's because it's not getting anything in this b column.
5:02 So what we're going to do is we're going to wrap this if is blank. And what's blank? We want the uhm b2, if it's blank, meaning there's no permutations to go through.
5:16 We just want the base. So if that value is true, meaning it is blank. We just want a 3. And if it's false, meaning there's something there.
5:25 It is not blank. Now we're gonna add together all of the stuff. And so we add that if blank. We'll copy paste down again.
5:32 We still have a value here I think because we have maybe a space. No. B4, B. Oh, I think because we have B2 here.
5:42 So we need that to be B3. Fix that. Now we have everything where we need it. So now we have automatically created any type of base we have here.
5:51 Let's say A. And we want to add 1, 4, 5, maybe 99. Now it's going to load up all of those permutations right here in this automatic ah cell.
6:03 So that's cool. That's how you create every possible permutation based on some text that you want. And if you know the base, and you know the permutations, that's how we all put it together.
6:11 And it looks really crazy, but hopefully from doing it from the ground up and using this ah spinAll function that we now have because of the spintext ah it is going to be super easy.
6:24 Easy for you to ah do something like this in your own sheets.
0:19 is here, C. So we can absolutely type this out, right? We can, we know if we have X, 1, 2, 3, we can do X, X and in this case they wanted a hyphen and then that number, that 1, 2, or 3.
0:31 So we can do X, 1, X hyphen 2, X hyphen 3. Absolutely we can type that by hand, right? But we don't want to do that by hand, we want to do this automatically, right?
0:41 Automatically get every possible permutation including the base one. So uhm here's the answer, this, this is going to be the entire formula.
0:48 We have a special SPINALL formula in here, we're going to use IF is blank, we're going to use JOIN, we're going to use SPLIT, uhm and SPIN or SPINALL is actually a custom function with a Google Sheet add-on that I myself created, it is absolutely free.
1:04 So if you're watching this anywhere on BetterSheets or on YouTube, check it out, you can go to add-ons, get add-ons and we're going to need to get this absolutely free Google Sheet add-on in order to complete it in this particular way.
1:15 There may be some other ways, but we're going to type in SPINTAX and if you type in just SPINTAX, you'll find it, SPINTAX Content Spinner, install it, it's absolutely free, uhm, and I just added actually some more stuff to it, but I'll get into that later, because right now we just want to spin this,
1:32 and what SPINTAX allows us to do is essentially take any kind of, uhm, this, not any kind, but a certain type of syntax, which we're going to use brackets, and then some x, and then a pipe to separate the options, let's say x.
1:52 X, y, z in this case. And now we have this bracket x, y, z. Well, say we want to do hello space and then x, hello y, hello z, and we want to separate those.
2:04 So here's what it would look like. So we would have hello and then we would do equals spin all, here's the output of that e3 here, e3.
2:19 And what it's going to do is it's going to put out hello z, hello y, hello x, right? In this case we can also add hello andrew, carl, zanzibar, right?
2:33 And now it's going to show hello zanzibar, hello carl, hello andrew, all of the permutations of it. So we need to, in this particular case, the person who was asking had these permutations 1, 2, 3 in these comments and we have to separate that and that's where we get, we're going to use split, we're
2:48 going to use join to combine them but we'll do a new result here and I will do it all from the ground up.
2:54 So first off we need to join by the pipe. So that's the, this up pipe thing. Put that in quotes.
3:07 And what are we going to join? Well we're going to join a split of this 1, 2, 3, 4. We're going to split, the delimiter is going to be a comma.
3:15 We are going to split by each. And now we see in our array, in our result, it's 1, 2, 3.
3:22 And now we need to concatenate. Actually we don't need concatenate here, but we can. We just use the ampersand and we can put in quotes the first curly bracket, the ampersand, another ampersand, and in quotes the end curly bracket.
3:39 And now we get the spin tax that we need. But we also need the base. So we need to do a 3 ampersand, and again, we're using ampersand here, but we can also use concatenate if we want to concatenate all these things together.
3:55 And now we have x3. But we want specifically, in quotes, a 3 a hyphen. And so we'll do that with ampersand as well, so we'll have x hyphen.
4:05 And now, this is our spin tax that we're going to now use, spin all around this, but we're going to get all of these in different rows, right?
4:18 So what we want to do is join them with a delimiter of a comma to separate, or whatever delimiter you want between these permutations.
4:28 What are we going to separate? We're going to have all these values from this spin all. There. Now we have them all in one place, but we're missing the base one.
4:37 And so we need to add the base. A three ampersand, and then a comma, and then a join. And then another ampersand, and then the join.
4:47 And so now we have, of The thing is, if we copy and paste this down, we'll see we have now the permutations of this y, this z.
4:56 But look at this. We have hashtag value. Why do we have hashtag value? It's because it's not getting anything in this b column.
5:02 So what we're going to do is we're going to wrap this if is blank. And what's blank? We want the uhm b2, if it's blank, meaning there's no permutations to go through.
5:16 We just want the base. So if that value is true, meaning it is blank. We just want a 3. And if it's false, meaning there's something there.
5:25 It is not blank. Now we're gonna add together all of the stuff. And so we add that if blank. We'll copy paste down again.
5:32 We still have a value here I think because we have maybe a space. No. B4, B. Oh, I think because we have B2 here.
5:42 So we need that to be B3. Fix that. Now we have everything where we need it. So now we have automatically created any type of base we have here.
5:51 Let's say A. And we want to add 1, 4, 5, maybe 99. Now it's going to load up all of those permutations right here in this automatic ah cell.
6:03 So that's cool. That's how you create every possible permutation based on some text that you want. And if you know the base, and you know the permutations, that's how we all put it together.
6:11 And it looks really crazy, but hopefully from doing it from the ground up and using this ah spinAll function that we now have because of the spintext ah it is going to be super easy.
6:24 Easy for you to ah do something like this in your own sheets.