Text string extraction

E

Excellis

New Member
Joined
Oct 30, 2012
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Can anyone help?
I'm after a function or combination of functions that can help me extract specific text from a known set of variables. For example, from the list below I might want to know how many blue flags are listed. I may also want to know how many white posters are listed etc.
The list (within each cell) will always be in alphabetical order by item, and the colours will always be in alphabetical order within the parentheses, but not all elements will be present at all times, as you can see.
In the list below each line represents a cell so the range could be C7:C22, for example

[TABLE="width: 425"]
<TBODY>[TR]
[TD]Poster (White)</SPAN>[/TD]
[/TR]
[TR]
[TD]Poster (Blue,Red,White), Flag (Red)</SPAN>[/TD]
[/TR]
[TR]
[TD]Leaflet (Red), Poster (Blue), Flag (Blue,White)</SPAN>[/TD]
[/TR]
[TR]
[TD]Card (Red), Sticker (Red), Flag (Blue,Red)</SPAN>[/TD]
[/TR]
[TR]
[TD]Sticker (Blue,Red,White), Bookmark (Blue)</SPAN>[/TD]
[/TR]
[TR]
[TD]Sticker (Red,White), Flag (Blue,White)</SPAN>[/TD]
[/TR]
[TR]
[TD]Sticker (Blue,Red,White), Flag (Blue)</SPAN>[/TD]
[/TR]
[TR]
[TD]Bookmark (Blue), Flag (Blue,Red,White)</SPAN>[/TD]
[/TR]
[TR]
[TD]Leaflet (Blue,Red), Poster (Blue,Red),Flag (Red)</SPAN>[/TD]
[/TR]
[TR]
[TD]Leaflet (Blue,Red,White), Poster (White)</SPAN>[/TD]
[/TR]
[TR]
[TD]Card (Red), Sticker (Red), Flag (Red)</SPAN>[/TD]
[/TR]
[TR]
[TD]Leaflet (Red), Sticker (Red), Flag (Red)</SPAN>[/TD]
[/TR]
[TR]
[TD]Leaflet (Red), Sticker (Red), Flag (Red)</SPAN>[/TD]
[/TR]
[TR]
[TD]Leaflet (Red), Sticker (Red)</SPAN>[/TD]
[/TR]
[TR]
[TD]Card (Blue), Flag (Red)</SPAN>[/TD]
[/TR]
[TR]
[TD]Leaflet (Red), Sticker (Red), Flag (Blue)

Thanks in advance
</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
As long as the Flag item, when present, is always the last item in the cell, you can use this formula to count the blue flags...

=COUNTIF(C7:C22,"*Flag*Blue*")
 
Upvote 0
Thanks, Rick. I tried that, but when I did the same for all the possible variations I ended up with a count of 62 items, whereas there are only actually 54 variables represented, so it seems to be double counting somewhere along the way.
 
Upvote 0
I'd do this:
Code: 
Public Function CountItems(rng As Range, Item As String, Colour As String, Optional IgnoreCase As Boolean) As Long


Static regex    As Object
Dim s_rng       As String


If regex Is Nothing Then Set regex = CreateObject("vbscript.regexp")


If rng.Count > 1 Then
    s_rng = Join(Application.Transpose(rng), ", ")
Else
    s_rng = rng
End If


With regex
    .Pattern = Item & "\s\([a-zA-z,]*" & Colour & "[a-zA-z,]*\)"
    .Global = True
    .IgnoreCase = IgnoreCase
    CountItems = .Execute(s_rng).Count
End With


End Function

In the cell, you can then use:
=CountItems($A$1:$A$16,"Flag","Blue")
=CountItems($A$1:$A$16,"Card","Red")

etc....
 
Upvote 0
Cheers, Kyle
I shall give that a try in a little while.

Regards
Tom
 
Upvote 0
Another possible solution

User Defined Function

Code: 
Function CountItems(r As Range, crit1 As String, crit2 As String) As Long
    Dim rCell As Range, strAux As String, s As Variant
    Dim i As Long
    
    For Each rCell In r
        strAux = Replace(Replace(rCell.Value, " ", ""), "),", ");")
        s = Split(strAux, ";")
        
        For i = LBound(s) To UBound(s)
            If InStr(s(i), crit1) > 0 And InStr(s(i), crit2) > 0 Then _
                CountItems = CountItems + 1
        Next i
        
    Next rCell
    
End Function

Usage

A B C D E
[TABLE="width: 424"]
<tbody>[TR]
[TD="class: xl63, width: 309, bgcolor: transparent"]Poster (White)
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Crit1
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Crit2
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Count
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Poster (Blue,Red,White), Flag (Red)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Leaflet
[/TD]
[TD="class: xl63, bgcolor: transparent"]Red
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Leaflet (Red), Poster (Blue), Flag (Blue,White)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Poster
[/TD]
[TD="class: xl63, bgcolor: transparent"]White
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Card (Red), Sticker (Red), Flag (Blue,Red)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Sticker
[/TD]
[TD="class: xl63, bgcolor: transparent"]Red
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Sticker (Blue,Red,White), Bookmark (Blue)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Sticker (Red,White), Flag (Blue,White)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Sticker (Blue,Red,White), Flag (Blue)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Bookmark (Blue), Flag (Blue,Red,White)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Leaflet (Blue,Red), Poster (Blue,Red),Flag (Red)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Leaflet (Blue,Red,White), Poster (White)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Card (Red), Sticker (Red), Flag (Red)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Leaflet (Red), Sticker (Red), Flag (Red)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Leaflet (Red), Sticker (Red), Flag (Red)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Leaflet (Red), Sticker (Red)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Card (Blue), Flag (Red)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Leaflet (Red), Sticker (Red), Flag (Blue)
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Type in C2, C3....the first criterium; in D2, D3... the second

Formula in E2
=CountItems($A$1:$A$16,C2,D2)
copy down

M.
 
Upvote 0
Another version using only built in functions:
Code: 
Public Function CountItems2(rng, Item As String, Colour As String, Optional IgnoreCase As Boolean) As Long


If rng.Count > 1 Then rng = Join(Application.Transpose(rng), ", ")
CountItems2 = UBound(Filter(Filter(Split(rng, ")"), Item), Colour)) + 1


End Function
 
Upvote 0
Thanks Kyle, (& Marcelo)
I'm new to this kind of thing so not sure how to thank you both correctly, but based on what you've both posted here I've managed to get a solution and saved myself hours of work, as functions/formulas are designed to do.

Thanks again
Tom
 
Upvote 0
Welcome to the MrExcel board!

See if using these standard worksheet functions counts correctly.

Excel Workbook
DEFGH
5ItemColourCount
6FlagBlue6
7Poster (White)
8Poster (Blue,Red,White), Flag (Red)
9Leaflet (Red), Poster (Blue), Flag (Blue,White)
10Card (Red), Sticker (Red), Flag (Blue,Red)
11Sticker (Blue,Red,White), Bookmark (Blue)
12Sticker (Red,White), Flag (Blue,White)
13Sticker (Blue,Red,White), Flag (Blue)
14Bookmark (Blue), Flag (Blue,Red,White)
15Leaflet (Blue,Red), Poster (Blue,Red),Flag (Red)
16Leaflet (Blue,Red,White), Poster (White)
17Card (Red), Sticker (Red), Flag (Red)
18Leaflet (Red), Sticker (Red), Flag (Red)
19Leaflet (Red), Sticker (Red), Flag (Red)
20Leaflet (Red), Sticker (Red)
21Card (Blue), Flag (Red)
22Leaflet (Red), Sticker (Red), Flag (Blue)
23
Count items Colours
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Solved
How to find a code within a string of text
2
Replies
14
Views
805
Fluff
Fluff
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
J
  • Question Question
VBA Code to Find Copy Rows From Source Sheet Based on Text and Paste on Target Sheet Corresponding Number of Times.
Replies
2
Views
385
JPatty
J
D
  • Question Question
XMATCH compare using unknown text
Replies
1
Views
781
Dmherl76
D

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top