Split Text String based on listing

L

Lenard

New Member
Joined
Jan 19, 2010
Messages
35
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I have a set of data in column A with text string in each row under Data sheet and would like to split text string in each row based on a listing of keywords ( Unique name ) under List sheet.
I copied set of formulas from google search and modified, the result is not met my expectation.

{=IFERROR(INDEX(List!$A$2:$A$71, SMALL(IF(COUNTIF($A2, "*"&List!$A$2:$A$71&"*"), MATCH(ROW(List!$A$2:$A$71), ROW(List!$A$2:$A$71)), ""), COLUMNS($M$1:M1))), "")}

Sample file_working.xlsx
A
1Keyword
2ABS
3ADDRESS
4AGGREGATE
5AND
6AVERAGE
7CELL
8COLUMN
9CONCAT
10COUNT
11COUNTA
12COUNTBLANK
13COUNTIF
14COUNTIFS
15DATE
16EDATE
17EOMONTH
18EXACT
19FILTER
20FIND
21FLOOR
22FREQUENCY
23HYPERLINK
24IF
25INDEX
26INDIRECT
27ISBLANK
28ISERR
29ISERROR
30ISNA
31ISNUMBER
32LARGE
33LEFT
34LEN
35LET
36LOWER
37MATCH
38MAX
39MID
40MIN
41MMULT
42MOD
43NOT
44ODD
45OFFSET
46OR
47POWER
48PROPER
49RANK
50REPT
51RIGHT
52ROW
53SEARCH
54SEQUENCE
55SMALL
56SORT
57SUBTOTAL
58SUM
59SUMIF
60SUMPRODUCT
61TEXT
62TODAY
63TRANSPOSE
64UNIQUE
65UPPER
66LOOKUP
67XLOOKUP
68HLOOKUP
69VLOOKUP
70WEEKDAY
71YEAR
List


Sample file_working.xlsx
ABCDEFGHIJ
1DataResults
2COUNTIFSFINDLEFTRIGHTCOUNTCOUNTIFCOUNTIFSFINDIFLEFTRIGHT
3COUNTIFSDATECOUNTCOUNTIFCOUNTIFSDATEIF  
4COUNTIFSCOUNTIFCOUNTCOUNTIFCOUNTIFSIF   
5COUNTIFCOUNTCOUNTIFIF    
6SUMPRODUCTEXACTEXACTSUMSUMPRODUCT    
7COUNTIFSUMPRODUCTISNUMBERMATCHCOUNTCOUNTIFCOUNTIFSIFISNUMBERMATCHSUMSUMPRODUCT
8COUNTIFSUMPRODUCTCOUNTCOUNTIFCOUNTIFSIFSUMSUMPRODUCT  
9MATCHISNASUMPRODUCTCOUNTACOUNTIFCOUNTCOUNTACOUNTIFIFISNAMATCHSUMSUMPRODUCT
10COUNTIFSSUMPRODUCTCOUNTCOUNTIFCOUNTIFSIFSUMSUMPRODUCT  
11SUMPRODUCTLENNLENSUMSUMPRODUCT     
12COUNTBLANKCOUNTACOUNTCOUNTACOUNTBLANK     
13COUNTACOUNTIFCOUNTIFSCOUNTCOUNTACOUNTIFCOUNTIFSIF   
14COUNTIFCOUNTIFSCOUNTCOUNTIFCOUNTIFSIF   
15SUMPRODUCTISNUMBERFINDFINDISNUMBERSUMSUMPRODUCT   
16COUNTIFSUMPRODUCTISNUMBERFINDCOUNTCOUNTIFCOUNTIFSFINDIFISNUMBERSUM
17SUMPRODUCTISERRORISERRSUMISERRISERRORORSUMSUMPRODUCT  
18COUNTSUMPRODUCTCOUNTSUMSUMPRODUCT    
19SUMPRODUCTMODMODSUMSUMPRODUCT    
20COUNTIFSUMPRODUCTFINDISNUMBERCOUNTCOUNTIFCOUNTIFSFINDIFISNUMBERSUMSUMPRODUCT
21COUNTIFISTEXTSUMPRODUCTCOUNTIFSCOUNTCOUNTIFCOUNTIFSIFSUMSUMPRODUCTTEXT
22ISERRORNOTSUMPRODUCTISERRISERRISERRORNOTORSUMSUMPRODUCT 
23ISNUMBERSEARCHMMULTTRANSPOSEISNUMBERMMULTSEARCHTRANSPOSE   
24SUMPRODUCTWEEKDAYSUMSUMPRODUCTWEEKDAY    
25YEARSUMPRODUCTSUMSUMPRODUCTYEAR    
26SUMPRODUCTSUMSUMPRODUCT     
27COUNTIFSCOUNTCOUNTIFCOUNTIFSIF   
28SUMPRODUCTCOUNTIFCOUNTCOUNTIFIFSUMSUMPRODUCT  
29SUMPRODUCTISNUMBERMATCHSEARCHISNUMBERMATCHSEARCHSUMSUMPRODUCT  
30SUMPRODUCTISNAMATCHISNAMATCHSUMSUMPRODUCT   
Data
Cell Formulas
RangeFormula
C21:I30,C20:J20,C14:I19,C7:J13,C2:I6C2=IFERROR(INDEX(List!$A$2:$A$71, SMALL(IF(COUNTIF($A2, "*"&List!$A$2:$A$71&"*"), MATCH(ROW(List!$A$2:$A$71), ROW(List!$A$2:$A$71)), ""), COLUMNS($M$1:M1))), "")
Press CTRL+SHIFT+ENTER to enter array formulas.



The expected result is to separate text string in each row by columnar result or the result can be concatenated with "+" as per sample below : -

1628573847220.png


Or Expected Results :-
1628573920300.png



Appreciate if someone can help to use better set of formulas or vba to solve the above problem
Thanks in advance

Regards
Lenard
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Try this UDF method:

Book1
ABC
1KeywordDataResults
2ABSCOUNTIFSFINDLEFTRIGHTCOUNT+COUNTIF+COUNTIFS+FIND+IF+LEFT+RIGHT
3ADDRESSCOUNTIFSDATECOUNT+COUNTIF+COUNTIFS+DATE+IF
4AGGREGATECOUNTIFSCOUNTIFCOUNT+COUNTIF+COUNTIFS+IF
5ANDCOUNTIFCOUNT+COUNTIF+IF
6AVERAGESUMPRODUCTEXACTEXACT+SUM+SUMPRODUCT
7CELLCOUNTIFSUMPRODUCTISNUMBERMATCHCOUNT+COUNTIF+COUNTIFS+IF+ISNUMBER+MATCH+SUM+SUMPRODUCT
8COLUMNCOUNTIFSUMPRODUCTCOUNT+COUNTIF+COUNTIFS+IF+SUM+SUMPRODUCT
9CONCATMATCHISNASUMPRODUCTCOUNTACOUNTIFCOUNT+COUNTA+COUNTIF+IF+ISNA+MATCH+SUM+SUMPRODUCT
10COUNTCOUNTIFSSUMPRODUCTCOUNT+COUNTIF+COUNTIFS+IF+SUM+SUMPRODUCT
11COUNTASUMPRODUCTLENNLEN+SUM+SUMPRODUCT
12COUNTBLANKCOUNTBLANKCOUNTACOUNT+COUNTA+COUNTBLANK
13COUNTIFCOUNTACOUNTIFCOUNTIFSCOUNT+COUNTA+COUNTIF+COUNTIFS+IF
14COUNTIFSCOUNTIFCOUNTIFSCOUNT+COUNTIF+COUNTIFS+IF
15DATESUMPRODUCTISNUMBERFINDFIND+ISNUMBER+SUM+SUMPRODUCT
16EDATECOUNTIFSUMPRODUCTISNUMBERFINDCOUNT+COUNTIF+COUNTIFS+FIND+IF+ISNUMBER+SUM+SUMPRODUCT
17EOMONTHSUMPRODUCTISERRORISERRSUMISERR+ISERROR+OR+SUM+SUMPRODUCT
18EXACTCOUNTSUMPRODUCTCOUNT+SUM+SUMPRODUCT
19FILTERSUMPRODUCTMODMOD+SUM+SUMPRODUCT
20FINDCOUNTIFSUMPRODUCTFINDISNUMBERCOUNT+COUNTIF+COUNTIFS+FIND+IF+ISNUMBER+SUM+SUMPRODUCT
21FLOORCOUNTIFISTEXTSUMPRODUCTCOUNTIFSCOUNT+COUNTIF+COUNTIFS+IF+SUM+SUMPRODUCT+TEXT
22FREQUENCYISERRORNOTSUMPRODUCTISERRISERR+ISERROR+NOT+OR+SUM+SUMPRODUCT
23HYPERLINKISNUMBERSEARCHMMULTTRANSPOSEISNUMBER+MMULT+SEARCH+TRANSPOSE
24IFSUMPRODUCTWEEKDAYSUM+SUMPRODUCT+WEEKDAY
25INDEXYEARSUMPRODUCTSUM+SUMPRODUCT+YEAR
26INDIRECTSUMPRODUCTSUM+SUMPRODUCT
27ISBLANKCOUNTIFSCOUNT+COUNTIF+COUNTIFS+IF
28ISERRSUMPRODUCTCOUNTIFCOUNT+COUNTIF+IF+SUM+SUMPRODUCT
29ISERRORSUMPRODUCTISNUMBERMATCHSEARCHISNUMBER+MATCH+SEARCH+SUM+SUMPRODUCT
30ISNASUMPRODUCTISNAMATCHISNA+MATCH+SUM+SUMPRODUCT
31ISNUMBER
32LARGE
33LEFT
34LEN
35LET
36LOWER
37MATCH
38MAX
39MID
40MIN
41MMULT
42MOD
43NOT
44ODD
45OFFSET
46OR
47POWER
48PROPER
49RANK
50REPT
51RIGHT
52ROW
53SEARCH
54SEQUENCE
55SMALL
56SORT
57SUBTOTAL
58SUM
59SUMIF
60SUMPRODUCT
61TEXT
62TODAY
63TRANSPOSE
64UNIQUE
65UPPER
66LOOKUP
67XLOOKUP
68HLOOKUP
69VLOOKUP
70WEEKDAY
71YEAR
Sheet1
Cell Formulas
RangeFormula
C2:C30C2=SplitText($A$2:$A$71,B2)


VBA Code: 
Function SplitText(Keywords As Range, Data As Range) As String
    Dim c As Range, s As String
    
    For Each c In Keywords
        If InStr(Data, c.Value) > 0 Then s = s & c.Value & "+"
    Next
    
    SplitText = ""
    If s <> "" Then SplitText = Left(s, Len(s) - 1)
End Function
 
Upvote 0
Hi,

Thanks for reply and the result is very close but not met the expected result.

For example, the expected result for

1) "COUNTIFSFINDLEFTRIGHT" is COUNTIFS+FIND+LEFT+RIGHT in stead of COUNT+COUNTIF+COUNTIFS+FIND+IF+LEFT+RIGHT.
2) "COUNTIFSCOUNTIF" is COUNTIFS+COUNTIFS in stead of COUNT+COUNTIF+COUNTIFS+IF
3) "COUNTIFSSUMPRODUCT" is COUNTIFS+SUMPRODUCT in stead of COUNT+COUNTIF+COUNTIFS+IF+SUM+SUMPRODUCT
4) "COUNTIFS" is COUNTIFS in stead of COUNT+COUNTIF+IF
5) "SUMPRODUCT" is SUMPRODUCT instead of SUM+SUMPRODUCT


Good try, thanks

Regards
Lenard
 
Upvote 0
My bad, I thought your minisheet data was sort of the expected results.

Since COUNTIFS would give a match for either COUNTIFS, COUNTIF, or IF, the only way I can think of, is to sort the keywords by its length and to check the keywords with the longest length first. Once a match is found, it will be 'removed' so it wouldn't match any shorter length keywords anymore.

Try this amended version:

VBA Code: 
Function SplitText(Keywords As Range, Data As Range) As String
    Dim c As Range, s As String, d As String, lv As ListView, i As Long
    Set lv = New ListView
    lv.ColumnHeaders.Add
    
    i = 1
    For Each c In Keywords
        lv.ListItems.Add Text:=Format(Len(c.Value), "0000000000")
        lv.ListItems(i).ListSubItems.Add Text:=c.Value
        i = i + 1
    Next
    lv.SortKey = 0
    lv.SortOrder = lvwDescending
    lv.sorted = True
    
    d = Data.Value
    For i = 1 To lv.ListItems.Count
        If InStr(d, lv.ListItems(i).ListSubItems(1).Text) > 0 Then
            s = s & lv.ListItems(i).ListSubItems(1).Text & "+"
            d = Replace(d, lv.ListItems(i).ListSubItems(1).Text, "")
        End If
    Next
    
    SplitText = ""
    If s <> "" Then SplitText = Left(s, Len(s) - 1)
End Function

Requires reference to 'Microsoft Windows Common Controls 6.0 (SP6)' or something similar if this exact version is not found. Maybe a different solution can be done with collections, but I just amended it with an existing code that I have that is using the ListView control for sorting. To add the reference, in VBA editor, click on Tools -> References... -> check 'Microsoft Windows Common Controls 6.0 (SP6) -> OK
 
Upvote 0
Hi,

Thanks again.

Sorry, I can not try the amended UDF "SplitText" as my Excel version 2019 VBA tools reference do not have MS Window Common Control SP6.

Is there any alternative or other option to replace ListView in VBA ?

Regards
Lenard
 
Upvote 0
Upvote 0
Hi

Thanks for your suggestion.

But I'm not familiar with vba code to add listview control to userform and not sure about MS VB6 common controls

Regards
Lenard
 
Upvote 0
Hi Lenard, you don't need Vba to add a listview control. Once you have created the userform, double click on it and you should see a floating toolbox somewhere, look for the Listview control and drag to create it on the form. I think once you do that the reference will be automatically added, as Yasser pointed out (thanks Yasser for that!). If you don't see the toolbox, try looking for the tool bar from the one of the menu options at the top, I can't remember off my head where is it located (not on my computer now).

Meanwhile, try to get this to work, if you still can't get it working, I'll try giving a different solution tomorrow.
 
Upvote 0
Hi,

I downloaded MS Windows Common Controls 6.0 (SP6) or MS VB6.0 Common Controls and run the patch, within the VBA reference dialog, I can't see the file MSCOMCTL.ocx. I searched and the file is simply not on the machine.

Regards
Lenard
 
Upvote 0
Thanks both of you.

I don't think listview control is available in toolbox as MS Windows Common Controls 6.0 or MS VB6 Common Controls patch is not successfully added to my machine.

I'm not sure why the file MSCOMCTL.ocx is not found in VBA reference dialog after running the patch

Regards
Lenard
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Diagonal Sum in excel table via VBA code
2
Replies
11
Views
492
myall_blues
myall_blues
R
  • Question Question
Populate list of categories
Replies
0
Views
134
RIZVI
R
M
  • Question Question
Excel - Identify number of business days for a specific month within a weekly date range
Replies
3
Views
720
duggie33
D
S
  • Question Question
VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns
Replies
1
Views
114
sijpie
sijpie
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
493
mlan1604
M

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

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