Find which digits are used in 10 digit numbers and count which number is used how many times in Excel

[rahulbuddy]

Hi All,

Apologies if this has been asked before but I was not able to find any other question relevant to my concern.
Need your kind help in one of my Excel projects where I have been given thousands of 10-digit numbers and I have to find out which specific digits are used along with which specific digit is used and how many times it has been used within that 10-digit number.

Attaching the workbook for your reference. Any help is highly appreciated.

Here is mini sheet,

sample-workbook.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Sr. No.	Number (it will be 10 digit)	All digits used number (out of total 10 digits)	0's used	1's used	2's used	3's used	4's used	5's used	6's used	7's used	8's used	9's used	Sum (Should always be 10)
2	1	99 11 5 666 11	9156	0	4	0	0	0	1	3	0	0	2	10
3	2	8745614447	874561	0	1	0	0	4	1	1	2	1	0	10
4	3	7744123894	7412389	0	1	1	1	3	0	0	2	1	1	10
5	4	8845964555	84596											0
6	5	4121745688												0
7	6	11448 597 31												0
8	7	237 49651 2 8												0
9	8	21 45 78 44 11												0
10	9	896 574 5621												0
11	10													0
12
13
14		Please note	The numbers in B coloums are compulsarily 10 digits only, but it may have spaces in between them like '23 55 444 111' or '666 222 0101' or '66 22 11 01 22' or '565 565 1212' like this
15
16
17
18			The C coloum should only contains how many unique digits are being used in column B
19
20
Sheet1

Cell Formulas
Range	Formula
N2:N11	N2	=SUM(D2:M2)

Thanks and Regards,
Rahul.

 

[etaf]

you posted on Welcome to Chandoo.org - Learn Excel, Charting Online where i answered

i added an extra row with 0-9 in
but could use LEFT() - see below

sample-workbook-etaf.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Sr. No.	Number (it will be 10 digit)	All digits used number (out of total 10 digits)	0's used	1's used	2's used	3's used	4's used	5's used	6's used	7's used	8's used	9's used	Sum (Should always be 10)
2	1	99 11 5 666 11	9156	0	4	0	0	0	1	3	0	0	2	10
3	2	8745614447	874561	0	1	0	0	4	1	1	2	1	0	10
4	3	7744123894	7412389	0	1	1	1	3	0	0	2	1	1	10
5	4	8845964555	84596	0	0	0	0	2	4	1	0	2	1	10
6	5	4121745688		0	2	1	0	2	1	1	1	2	0	10
7	6	11448 597 31		0	3	0	1	2	1	0	1	1	1	10
8	7	237 49651 2 8		0	1	2	1	1	1	1	1	1	1	10
9	8	21 45 78 44 11		0	3	1	0	3	1	0	1	1	0	10
10	9	896 574 5621		0	1	1	0	1	2	2	1	1	1	10
11	10			0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
D2:M11	D2	=LEN($B2)-LEN(SUBSTITUTE($B2,LEFT(D$1,1),""))
N2:N11	N2	=SUM(D2:M2)

sample-workbook-etaf.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Sr. No.	Number (it will be 10 digit)	All digits used number (out of total 10 digits)	0's used	1's used	2's used	3's used	4's used	5's used	6's used	7's used	8's used	9's used	Sum (Should always be 10)
2				0	1	2	3	4	5	6	7	8	9
3	1	99 11 5 666 11	9156	0	4	0	0	0	1	3	0	0	2	10
4	2	8745614447	874561	0	1	0	0	4	1	1	2	1	0	10
5	3	7744123894	7412389	0	1	1	1	3	0	0	2	1	1	10
6	4	8845964555	84596	0	0	0	0	2	4	1	0	2	1	10
7	5	4121745688		0	2	1	0	2	1	1	1	2	0	10
8	6	11448 597 31		0	3	0	1	2	1	0	1	1	1	10
9	7	237 49651 2 8		0	1	2	1	1	1	1	1	1	1	10
10	8	21 45 78 44 11		0	3	1	0	3	1	0	1	1	0	10
11	9	896 574 5621		0	1	1	0	1	2	2	1	1	1	10
12	10			0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
D3:M12	D3	=LEN($B3)-LEN(SUBSTITUTE($B3,D$2,""))
N3:N12	N3	=SUM(D3:M3)

 

[DanteAmor]

Try:

varios 21jun2023.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Sr. No.	Number (it will be 10 digit)	All digits used number (out of total 10 digits)	0's used	1's used	2's used	3's used	4's used	5's used	6's used	7's used	8's used	9's used	Sum (Should always be 10)
2	1	99 11 5 666 11	9156	0	4	0	0	0	1	3	0	0	2	10
3	2	8745614447	874561	0	1	0	0	4	1	1	2	1	0	10
4	3	7744123894	7412389	0	1	1	1	3	0	0	2	1	1	10
5	4	8845964555	84596	0	0	0	0	2	4	1	0	2	1	10
6	5	4121745688	4127568	0	2	1	0	2	1	1	1	2	0	10
7	6	11448 597 31	1485973	0	3	0	1	2	1	0	1	1	1	10
8	7	237 49651 2 8	237496518	0	1	2	1	1	1	1	1	1	1	10
9	8	21 45 78 44 11	214578	0	3	1	0	3	1	0	1	1	0	10
10	9	896 574 5621	89657421	0	1	1	0	1	2	2	1	1	1	10
11	10	1234567890	1234567890	1	1	1	1	1	1	1	1	1	1	10
Hoja1

Cell Formulas
Range	Formula
D2:M11	D2	=LEN($B2)-LEN(SUBSTITUTE($B2,COLUMNS($D$1:D$1)-1,""))
N2:N11	N2	=SUM(D2:M2)

In cell C2 and copy down:

=SUBSTITUTE(TEXTJOIN("",, UNIQUE(MID(B2,SEQUENCE(LEN(B2)),1)))," ","")

 

[Joe4]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Find which digits are used in 10 digit numbers and count which number is used how many times in Excel
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[alansidman]

also crossposted at: Find which digits are used in 10 digit number and count which number is used how many time

 

[rahulbuddy]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Find which digits are used in 10 digit numbers and count which number is used how many times in Excel
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hello,
Apologies for that. I am completely new to the forums and was in a hurry to submit my assignments before the deadline and wanted help as soon as possible, that was the only intention.
I will make sure that I comply with the forum rules in coming future.

How can I mark this forum as SOLVED as I have got what I wanted? Or how can I stop the replies?

Thanks & Regards
Rahul.