Delimit and count number of instances

[excel_2009]

Hi excel gurus!

I'm struggling to find any resources on this but is it at all possible to do the following:

I have rows of text which contain semicolons after each phrase, I simply want to count the number of instances of these phrases for example:

Hello;Error;T-Shirt
Hello
Error;T-Shirt
Jumper;Hello how are you

The output would be:

Hello	2
Error	2
T-Shirt	2
Jumper	1
Hello how are you	1

<tbody>
</tbody>

Is this possible? if so how?

Many thanks!!!!

 

[Aladin Akyurek]

Row\Col	A​	B​
1​	Hello;Error;T-Shirt
2​	Hello
3​	Error;T-Shirt
4​	Jumper;Hello how are you
5​
6​	The output would be:
7​
8​	Hello	2​
9​	Error	2​
10​	T-Shirt	2​
11​	Jumper	1​
12​	Hello how are you	1​

In B8 enter and copy down:

=SUMPRODUCT(ISNUMBER(SEARCH(";"&SUBSTITUTE(A8," ","")&";",";"&SUBSTITUTE($A$1:$A$4," ","")&";"))+0)

 

[MickG]

Try this for results starting "C1".

[COLOR="Navy"]Sub[/COLOR] MG03Apr06
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Sp = Split(Dn.Value, ";")
            [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
                [COLOR="Navy"]If[/COLOR] Not .Exists(Sp(n)) [COLOR="Navy"]Then[/COLOR]
                    .Add Sp(n), 1
                [COLOR="Navy"]Else[/COLOR]
                    .Item(Sp(n)) = .Item(Sp(n)) + 1
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]Next[/COLOR]
Range("C1").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .items))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[excel_2009]

Thank you Aladin and Mick! both worked perfectly!!!