Count unique characters

[jts2004]

ex: cell A1 has the text "AABBCCDDEFGAB" I want to count the "A"'s , "B"'s ect...

 

[meldoc]

Hello

See the link.

http://www.youtube.com/user/ExcelIsFun#p/search/10/8NKPV05tfis

 

[Andrew Poulsom]

If you want to count the number of unique characters try this UDF:

Function UniqueChars(Str As String)
    Dim i As Integer
    Dim Chars As New Collection
    On Error Resume Next
    For i = 1 To Len(Str)
        Chars.Add Mid(Str, i, 1), Mid(Str, i, 1)
    Next i
    UniqueChars = Chars.Count
End Function

Use it on your worksheet like:

=UniqueChars(A1)

 

[jts2004]

Is there a formula? I can't get YouTube at work

 

[mike31z]

Here is a copy of a small worksheet .

aaabbbccccddddeeeeffff
3 a =REGEX.COUNT($A$1,"a")
3 b =REGEX.COUNT($A$1,"b")
4 c =REGEX.COUNT($A$1,"b")
4 d =REGEX.COUNT($A$1,"e")
4 e =REGEX.COUNT($A$1,"f")
3 a =REGEX.COUNT(A1,"a",FALSE)
4 b =REGEX.COUNT($A$1,"b",FALSE)
4 c =REGEX.COUNT($A$1,"c",FALSE)
4 d =REGEX.COUNT($A$1,"d",FALSE)
4 e =REGEX.COUNT($A$1,"e",FALSE)
4 f =REGEX.COUNT($A$1,"f",FALSE)

The REGEX.COUNT is part of the Morefunc Excel addin you can find the link below. There is true function that checks for upper or lower case or a false that will count both.


http://www.mrexcel.com/forum/showthread.php?t=126629


I hope that helps

mike in wisconsin

 

[jts2004]

Thanks everyone. All of these are good options

 

[Andrew Poulsom]

If you only want to count the number of A's:

=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))

 

[MickG]

Hi, Perhaps a bit late, but this will give all results in columns "C & D".

[COLOR="Navy"]Sub[/COLOR] MG26Jan32
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] n = 1 To Len([a1])
    [COLOR="Navy"]If[/COLOR] Not .Exists(Mid([a1], n, 1)) [COLOR="Navy"]Then[/COLOR]
        .Add Mid([a1], n, 1), 1
    [COLOR="Navy"]Else[/COLOR]
        .Item(Mid([a1], n, 1)) = .Item(Mid([a1], n, 1)) + 1
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("C1").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .items))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jts2004]

If you only want to count the number of A's:

=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))

this works great. thx