What formula in excel can I use to automatically show every 4 digit combination

[KSZEN]

Can anyone tell me of a formula that will list every 4 digit combination of a 4 digit number entered

So if I input 1234 in cell A1
and hit enter

I would like a listing of all of the 4 digit combinations using the numbers entered

Thanks

[6StringJazzer]

Put this formula in row 1 and fill down to row 256:

=MID($A$1,INT((ROW()-1)/64)+1,1)&MID($A$1,MOD(INT((ROW()-1)/16),4)+1,1)&MID($A$1,MOD(INT((ROW()-1)/4),4)+1,1)&MID($A$1,MOD(ROW()-1,4)+1,1)

Just to be clear, this gives combinations, which is what you asked for, and not permutations.

[KSZEN]

Thank you for that formula but I didn't explain what I needed properly

If I input (4512), what I only want to see is all combinations of (4512) like 4125,2514,1542,1452 etc
I want the listed combinations to only come from the number I inputed

[6StringJazzer]

Are you saying that you want to see only permutations? (Please look up permutations vs. combinations)

[6StringJazzer]

Here is a quick & dirty way that is practical for 4 digits. The number of permutations gets big fast when you add digits. My second choice would be to do this with VBA. Let me know if you would like to see that.

In this example I used abcd instead of digits so it would not be confused with the position selectors in column C. But you can put any four characters in A1 for this to work.
Values as displayed

	A	B	C
1	abcd	abcd	1234
2		abdc	1243
3		acbd	1324
4		acdb	1342
5		adbc	1423
6		adcb	1432
7		bacd	2134
8		badc	2143
9		bcad	2314
10		bcda	2341
11		bdac	2413
12		bdac	2413
13		cabd	3124
14		cadb	3142
15		cbad	3214
16		cbda	3241
17		cdab	3412
18		cdba	3421
19		dabc	4123
20		dacb	4132
21		dbac	4213
22		dbca	4231
23		dcab	4312
24		dcba	4321

Underlying formulas

	A	B	C
1	abcd	=MID($A$1,MID(C1,1,1),1)&MID($A$1,MID(C1,2,1),1)&MID($A$1,MID(C1,3,1),1)&MID($A$1,MID(C1,4,1),1)	1234
2		=MID($A$1,MID(C2,1,1),1)&MID($A$1,MID(C2,2,1),1)&MID($A$1,MID(C2,3,1),1)&MID($A$1,MID(C2,4,1),1)	1243
3		=MID($A$1,MID(C3,1,1),1)&MID($A$1,MID(C3,2,1),1)&MID($A$1,MID(C3,3,1),1)&MID($A$1,MID(C3,4,1),1)	1324
4		=MID($A$1,MID(C4,1,1),1)&MID($A$1,MID(C4,2,1),1)&MID($A$1,MID(C4,3,1),1)&MID($A$1,MID(C4,4,1),1)	1342
5		=MID($A$1,MID(C5,1,1),1)&MID($A$1,MID(C5,2,1),1)&MID($A$1,MID(C5,3,1),1)&MID($A$1,MID(C5,4,1),1)	1423
6		=MID($A$1,MID(C6,1,1),1)&MID($A$1,MID(C6,2,1),1)&MID($A$1,MID(C6,3,1),1)&MID($A$1,MID(C6,4,1),1)	1432
7		=MID($A$1,MID(C7,1,1),1)&MID($A$1,MID(C7,2,1),1)&MID($A$1,MID(C7,3,1),1)&MID($A$1,MID(C7,4,1),1)	2134
8		=MID($A$1,MID(C8,1,1),1)&MID($A$1,MID(C8,2,1),1)&MID($A$1,MID(C8,3,1),1)&MID($A$1,MID(C8,4,1),1)	2143
9		=MID($A$1,MID(C9,1,1),1)&MID($A$1,MID(C9,2,1),1)&MID($A$1,MID(C9,3,1),1)&MID($A$1,MID(C9,4,1),1)	2314
10		=MID($A$1,MID(C10,1,1),1)&MID($A$1,MID(C10,2,1),1)&MID($A$1,MID(C10,3,1),1)&MID($A$1,MID(C10,4,1),1)	2341
11		=MID($A$1,MID(C11,1,1),1)&MID($A$1,MID(C11,2,1),1)&MID($A$1,MID(C11,3,1),1)&MID($A$1,MID(C11,4,1),1)	2413
12		=MID($A$1,MID(C12,1,1),1)&MID($A$1,MID(C12,2,1),1)&MID($A$1,MID(C12,3,1),1)&MID($A$1,MID(C12,4,1),1)	2413
13		=MID($A$1,MID(C13,1,1),1)&MID($A$1,MID(C13,2,1),1)&MID($A$1,MID(C13,3,1),1)&MID($A$1,MID(C13,4,1),1)	3124
14		=MID($A$1,MID(C14,1,1),1)&MID($A$1,MID(C14,2,1),1)&MID($A$1,MID(C14,3,1),1)&MID($A$1,MID(C14,4,1),1)	3142
15		=MID($A$1,MID(C15,1,1),1)&MID($A$1,MID(C15,2,1),1)&MID($A$1,MID(C15,3,1),1)&MID($A$1,MID(C15,4,1),1)	3214
16		=MID($A$1,MID(C16,1,1),1)&MID($A$1,MID(C16,2,1),1)&MID($A$1,MID(C16,3,1),1)&MID($A$1,MID(C16,4,1),1)	3241
17		=MID($A$1,MID(C17,1,1),1)&MID($A$1,MID(C17,2,1),1)&MID($A$1,MID(C17,3,1),1)&MID($A$1,MID(C17,4,1),1)	3412
18		=MID($A$1,MID(C18,1,1),1)&MID($A$1,MID(C18,2,1),1)&MID($A$1,MID(C18,3,1),1)&MID($A$1,MID(C18,4,1),1)	3421
19		=MID($A$1,MID(C19,1,1),1)&MID($A$1,MID(C19,2,1),1)&MID($A$1,MID(C19,3,1),1)&MID($A$1,MID(C19,4,1),1)	4123
20		=MID($A$1,MID(C20,1,1),1)&MID($A$1,MID(C20,2,1),1)&MID($A$1,MID(C20,3,1),1)&MID($A$1,MID(C20,4,1),1)	4132
21		=MID($A$1,MID(C21,1,1),1)&MID($A$1,MID(C21,2,1),1)&MID($A$1,MID(C21,3,1),1)&MID($A$1,MID(C21,4,1),1)	4213
22		=MID($A$1,MID(C22,1,1),1)&MID($A$1,MID(C22,2,1),1)&MID($A$1,MID(C22,3,1),1)&MID($A$1,MID(C22,4,1),1)	4231
23		=MID($A$1,MID(C23,1,1),1)&MID($A$1,MID(C23,2,1),1)&MID($A$1,MID(C23,3,1),1)&MID($A$1,MID(C23,4,1),1)	4312
24		=MID($A$1,MID(C24,1,1),1)&MID($A$1,MID(C24,2,1),1)&MID($A$1,MID(C24,3,1),1)&MID($A$1,MID(C24,4,1),1)	4321

[KSZEN]

Thank you this is exactly what I want.
Could you show me the VBA way also.

[6StringJazzer]

I got this on another site. I have not had time to go through it in detail. I would prefer a UDF but this works. It prompts for the string instead of reading it from a cell. It would be easy to change that.

Please Login or Register  to view this content.