-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Count unique characters
- Thread starter jts2004
- Start date
Upvote
0
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
If you want to count the number of unique characters try this UDF:
Use it on your worksheet like:
=UniqueChars(A1)
Code:
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 FunctionUse it on your worksheet like:
=UniqueChars(A1)
Upvote
0
mike31z
Board Regular
- Joined
- May 8, 2007
- Messages
- 149
- Office Version
- 2019
- Platform
- Windows
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
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
Upvote
0
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
If you only want to count the number of A's:
=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))
=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))
Upvote
0
Hi, Perhaps a bit late, but this will give all results in columns "C & D".
Regards Mick
Code:
[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]
Last edited:
Upvote
0
this works great. thx
Upvote
0
Similar threads
- Question
