2

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.

Valark
  • 43
  • 1
  • 4
  • It is, but a formula for it would be way too cumbersome and large. Best turn to VBA for it. Formula wise If your left column doesn't have very long entries you can dedicate some columns to hold 1 symbol of the first columnm then clear the duplicates in rows containing the spread out symbols and count how many cells in a row are not empty. Or you can match every symbol you could have vs the content for every cell. IF its just english letters and numbers thats 35 matches per cell. – helena4 Nov 19 '15 at 22:05

4 Answers4

7

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • And even longer if since you have to include lowercase which breaks your concept, thats hoping he doesn't use any other symbols or languages. If you really really insist to have a formula for it i can make it work for you... – helena4 Nov 19 '15 at 22:20
  • @helena4 feel free to post your own, if it is better and more concise I will encourage the OP to select yours. I am not greedy, and would love to learn something new. – Scott Craner Nov 19 '15 at 22:22
  • @helena4 so I fixed the lowercase issue, but again if you have other suggestions, feel free to advise or post. – Scott Craner Nov 19 '15 at 22:26
  • Thats the point - using a formula for such a thing should only be done if his sample base is very narrow. Lets wait and see if there is even a need to bother with formulas at all or if your solution can fit the actual data that needs to be worked. – helena4 Nov 19 '15 at 22:27
  • @helena4 hence the reason I also gave a UDF. It may be easier to count everything and subtract unwanted counted characters, like spaces. – Scott Craner Nov 19 '15 at 22:29
  • Works perfectly, thank you! I'm working with a fairly limited character set, so I'll end up using the formula. But it's also good to see the UDF and that consideration is being given for wider use cases. I'm sure it'll be useful information for future projects and those with similar problems. – Valark Nov 19 '15 at 22:41
  • You snuck it in :) And don't worry your initial formula was more elegant - i would have just made a monolithic contraption of search checks to avoid the case sensitivity issue you have. As you count A an a differently and he may not want to. And heck no - anybody that would produce an accurate flexible formula for that kind of thing should be slapped in the face and forced to reconsider their life priorities :) Vba is just unavoidable if you really have to cut corners with data. I'll bump you up too. – helena4 Nov 19 '15 at 22:41
3

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.

A W
  • 41
  • 1
1
=ROWS(UNIQUE(MID(A1,SEQUENCE(LEN(A1)),1)))

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

user11632362
  • 11
  • 3
0

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.

Dhamo
  • 1,171
  • 3
  • 19
  • 41
Don Axon
  • 1