CODEWITHSUNDEEP
excel-formulaexcel-2011
Valark
Valark

Reputation: 43

Is it possible to count the number of unique characters within a cell?

Software used: Excel Mac 2011

I have a column of cells containing alphanumeric strings, and I'm looking to count the number of unique characters that appear in each cell. I'd like to have it function as pictured below:

333333333 = 1; BEE = 2; DOG = 3; BREED = 4; APPLEPEOPLE = 5; ABC123 = 6

Because of the data I'm working with, I don't need spaces be included or excluded from the character count or any distinctions to be made between uppercase or lowercase characters.

Thanks for your help.

Upvotes: 2

Views: 10648

Answers (4)

user11632362
user11632362

Reputation: 11

=ROWS(UNIQUE(MID(A1,SEQUENCE(LEN(A1)),1)))

should work assuming you have access to these newer functions (i.e. UNIQUE and SEQUENCE)

Upvotes: 1

Don Axon
Don Axon

Reputation: 1

This allows you to count how many characters appear in the chart on another sheet I called data.

=SUMPRODUCT(--ISNUMBER(SEARCH(Data!$a$1:$a$26,A1)))

The best of this is you can add or change the list of characters easily, as long as you keep the range correct.

Upvotes: 0

A W
A W

Reputation: 41

I have come up with a new, much cleaner version of the formula first put forward by Scott.

=SUM(IF(LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),CHAR(ROW(INDIRECT("1:255"))),"")),1,0))

This will find all unique ASCII characters. The ROW(INDIRECT("1:255")) creates an array of numbers from 1 to 255, which I use CHAR to convert into the full character set without having to manually type all 255 characters.

If this is my only contribution to society, I can die happy.

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152660

Try this:

=SUM(IF((LEN(G13)-LEN(SUBSTITUTE(UPPER(G13),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","1","2","3","4","5","6","7","8","9","0"},""))),1,0))

As stated it is quite long. This will count the English Alpha-numeric characters both upper and lower, but as helena4 pointed out, you will need to add any other symbol to the array in the formula or they will not be counted.

Obviously change the G13 reference to the cell with the desired text to count.

If you want a UDF use this:

Function Uniquecount(Rng As Range) As Integer
Dim cUnique As Collection
Dim i As Integer
Set cUnique = New Collection

On Error Resume Next
For i = 1 To Len(Rng)
    cUnique.Add CStr(Mid(Rng, i, 1)), CStr(Mid(Rng, i, 1))
Next i
On Error GoTo 0

Uniquecount = cUnique.Count
End Function

Put it in a module attached to the workbook. Then call it like any other formula: =Uniquecount(G13)

This will count everything once, including spaces.

Upvotes: 7

Related Questions