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.



Thanks and Regards,
Rahul.

 

[ETAF]

i have added a row 2 with 0-9 in - but yuo could just hard code in the formula
and used
=LEN($B3)-LEN(SUBSTITUTE($B3,D$2,""))
and copy cross and down

used LEFT() instead of an extra row
=LEN($B2)-LEN(SUBSTITUTE($B2,LEFT(D$1,1),""))

 

[bosco_yip]

Another option.

Used COLUMN(A1)-1 to generate a series of number 0,1,2,3.....8,9

Then,

In D2, enter formula copied across right and down:

=LEN($B2)-LEN(SUBSTITUTE($B2,COLUMN(A$1)-1,""))

or this shortened way.

=LEN($B2)-LEN(SUBSTITUTE($B2,COLUMN(A$1)-1,))