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:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Hello[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Error[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]T-Shirt[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Jumper[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Hello how are you[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Is this possible? if so how?

Many thanks!!!!

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td]Hello;Error;T-Shirt[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]Hello[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]Error;T-Shirt[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]Jumper;Hello how are you[/td][td][/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]The output would be:[/td][td][/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]Hello[/td][td]

2​

[/td][/tr]
[tr][td]

9​

[/td][td]Error[/td][td]

2​

[/td][/tr]
[tr][td]

10​

[/td][td]T-Shirt[/td][td]

2​

[/td][/tr]
[tr][td]

11​

[/td][td]Jumper[/td][td]

1​

[/td][/tr]
[tr][td]

12​

[/td][td]Hello how are you[/td][td]

1​

[/td][/tr]
[/table]

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!!!