Part Number jumbling to give all possible permutations

[ellison]

Hi,

Is it possible for Excel to "jumble up" a part number & give all of its possible permutations?
In other words...
We have some slightly fat fingered colleagues who have been typing part numbers up with all of the right digits, letters (a-z, A-Z) and characters (dashes, slashes, spaces, commas etc) but the problem is that they have not necessarily typed them in the right order!

So if a part number is nice and short AB12, we could sometimes check whether any of our colleagues have entered one on of the different permutations of that part number =
i) AB12
ii) BA12
iii) B1A2
iv) B12A
v) A1B2
vi) A12B
vii) AB21
etc etc

I'm beginning to lose count of how many different permutations there are

And I'm getting really quite lost when it comes to extrapolating the possibilities on longer part numbers (some of our part numbers contain 25+ characters)

In an ideal world, Excel could do this using a macro with a results sheet which had 2 fields eg

Original Part Number	Outcomes
AB12	AB12
AB12	BA12
AB12	B1A2
	etc, etc

Huge thanks for any help you can provide (especially from my fat fingered colleagues!)

 

[NdNoviceHlp]

Hi ellison. You can use the random function to generate possible permutations which likely would work for part numbers having 4 or 5 characters but for part numbers with 25 characters it would seem impossible. The possible permutations for a 5 character part number would be 1x2x3x4x5 which would be 120 possible permutations. If U had 25 characters, I can't do the math as the possible permutations are too numerous. I'm guessing it would be better to limit the input of part numbers to existing part numbers which probably doesn't help U now. Good luck. Dave

 

[ellison]

Hi ellison. You can use the random function to generate possible permutations which likely would work for part numbers having 4 or 5 characters but for part numbers with 25 characters it would seem impossible. The possible permutations for a 5 character part number would be 1x2x3x4x5 which would be 120 possible permutations. If U had 25 characters, I can't do the math as the possible permutations are too numerous. I'm guessing it would be better to limit the input of part numbers to existing part numbers which probably doesn't help U now. Good luck. Dave

Crumbs I thought that may be the case, was holding out hope that Excel may be able to help find a workaround.

Thanks for your input though, appreciated!

 

[sdear]

Crumbs I thought that may be the case, was holding out hope that Excel may be able to help find a workaround.

Thanks for your input though, appreciated!

Here's something I found in my searchings:
How to generate or list all possible permutations in Excel?

It does up to 7 characters (5040 permutations, or cells in a column).
You'd have a problem with 10 characters because it'd be more lines than what Excel would allow (~1million).

 

[ellison]

Aw thanks so much, that is excellent and will come in very handy!
All the best