Split Text String based on listing

[Lenard]

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

Sample file_working.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Data		Results
2	COUNTIFSFINDLEFTRIGHT		COUNT	COUNTIF	COUNTIFS	FIND	IF	LEFT	RIGHT
3	COUNTIFSDATE		COUNT	COUNTIF	COUNTIFS	DATE	IF
4	COUNTIFSCOUNTIF		COUNT	COUNTIF	COUNTIFS	IF
5	COUNTIF		COUNT	COUNTIF	IF
6	SUMPRODUCTEXACT		EXACT	SUM	SUMPRODUCT
7	COUNTIFSUMPRODUCTISNUMBERMATCH		COUNT	COUNTIF	COUNTIFS	IF	ISNUMBER	MATCH	SUM	SUMPRODUCT
8	COUNTIFSUMPRODUCT		COUNT	COUNTIF	COUNTIFS	IF	SUM	SUMPRODUCT
9	MATCHISNASUMPRODUCTCOUNTACOUNTIF		COUNT	COUNTA	COUNTIF	IF	ISNA	MATCH	SUM	SUMPRODUCT
10	COUNTIFSSUMPRODUCT		COUNT	COUNTIF	COUNTIFS	IF	SUM	SUMPRODUCT
11	SUMPRODUCTLENN		LEN	SUM	SUMPRODUCT
12	COUNTBLANKCOUNTA		COUNT	COUNTA	COUNTBLANK
13	COUNTACOUNTIFCOUNTIFS		COUNT	COUNTA	COUNTIF	COUNTIFS	IF
14	COUNTIFCOUNTIFS		COUNT	COUNTIF	COUNTIFS	IF
15	SUMPRODUCTISNUMBERFIND		FIND	ISNUMBER	SUM	SUMPRODUCT
16	COUNTIFSUMPRODUCTISNUMBERFIND		COUNT	COUNTIF	COUNTIFS	FIND	IF	ISNUMBER	SUM
17	SUMPRODUCTISERRORISERRSUM		ISERR	ISERROR	OR	SUM	SUMPRODUCT
18	COUNTSUMPRODUCT		COUNT	SUM	SUMPRODUCT
19	SUMPRODUCTMOD		MOD	SUM	SUMPRODUCT
20	COUNTIFSUMPRODUCTFINDISNUMBER		COUNT	COUNTIF	COUNTIFS	FIND	IF	ISNUMBER	SUM	SUMPRODUCT
21	COUNTIFISTEXTSUMPRODUCTCOUNTIFS		COUNT	COUNTIF	COUNTIFS	IF	SUM	SUMPRODUCT	TEXT
22	ISERRORNOTSUMPRODUCTISERR		ISERR	ISERROR	NOT	OR	SUM	SUMPRODUCT
23	ISNUMBERSEARCHMMULTTRANSPOSE		ISNUMBER	MMULT	SEARCH	TRANSPOSE
24	SUMPRODUCTWEEKDAY		SUM	SUMPRODUCT	WEEKDAY
25	YEARSUMPRODUCT		SUM	SUMPRODUCT	YEAR
26	SUMPRODUCT		SUM	SUMPRODUCT
27	COUNTIFS		COUNT	COUNTIF	COUNTIFS	IF
28	SUMPRODUCTCOUNTIF		COUNT	COUNTIF	IF	SUM	SUMPRODUCT
29	SUMPRODUCTISNUMBERMATCHSEARCH		ISNUMBER	MATCH	SEARCH	SUM	SUMPRODUCT
30	SUMPRODUCTISNAMATCH		ISNA	MATCH	SUM	SUMPRODUCT
Data

Cell Formulas
Range	Formula
C21:I30,C20:J20,C14:I19,C7:J13,C2:I6	C2	=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 : -

Or Expected Results :-

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

Regards
Lenard

 

[aRandomHelper]

Try this UDF method:

Book1
	A	B	C
1	Keyword	Data	Results
2	ABS	COUNTIFSFINDLEFTRIGHT	COUNT+COUNTIF+COUNTIFS+FIND+IF+LEFT+RIGHT
3	ADDRESS	COUNTIFSDATE	COUNT+COUNTIF+COUNTIFS+DATE+IF
4	AGGREGATE	COUNTIFSCOUNTIF	COUNT+COUNTIF+COUNTIFS+IF
5	AND	COUNTIF	COUNT+COUNTIF+IF
6	AVERAGE	SUMPRODUCTEXACT	EXACT+SUM+SUMPRODUCT
7	CELL	COUNTIFSUMPRODUCTISNUMBERMATCH	COUNT+COUNTIF+COUNTIFS+IF+ISNUMBER+MATCH+SUM+SUMPRODUCT
8	COLUMN	COUNTIFSUMPRODUCT	COUNT+COUNTIF+COUNTIFS+IF+SUM+SUMPRODUCT
9	CONCAT	MATCHISNASUMPRODUCTCOUNTACOUNTIF	COUNT+COUNTA+COUNTIF+IF+ISNA+MATCH+SUM+SUMPRODUCT
10	COUNT	COUNTIFSSUMPRODUCT	COUNT+COUNTIF+COUNTIFS+IF+SUM+SUMPRODUCT
11	COUNTA	SUMPRODUCTLENN	LEN+SUM+SUMPRODUCT
12	COUNTBLANK	COUNTBLANKCOUNTA	COUNT+COUNTA+COUNTBLANK
13	COUNTIF	COUNTACOUNTIFCOUNTIFS	COUNT+COUNTA+COUNTIF+COUNTIFS+IF
14	COUNTIFS	COUNTIFCOUNTIFS	COUNT+COUNTIF+COUNTIFS+IF
15	DATE	SUMPRODUCTISNUMBERFIND	FIND+ISNUMBER+SUM+SUMPRODUCT
16	EDATE	COUNTIFSUMPRODUCTISNUMBERFIND	COUNT+COUNTIF+COUNTIFS+FIND+IF+ISNUMBER+SUM+SUMPRODUCT
17	EOMONTH	SUMPRODUCTISERRORISERRSUM	ISERR+ISERROR+OR+SUM+SUMPRODUCT
18	EXACT	COUNTSUMPRODUCT	COUNT+SUM+SUMPRODUCT
19	FILTER	SUMPRODUCTMOD	MOD+SUM+SUMPRODUCT
20	FIND	COUNTIFSUMPRODUCTFINDISNUMBER	COUNT+COUNTIF+COUNTIFS+FIND+IF+ISNUMBER+SUM+SUMPRODUCT
21	FLOOR	COUNTIFISTEXTSUMPRODUCTCOUNTIFS	COUNT+COUNTIF+COUNTIFS+IF+SUM+SUMPRODUCT+TEXT
22	FREQUENCY	ISERRORNOTSUMPRODUCTISERR	ISERR+ISERROR+NOT+OR+SUM+SUMPRODUCT
23	HYPERLINK	ISNUMBERSEARCHMMULTTRANSPOSE	ISNUMBER+MMULT+SEARCH+TRANSPOSE
24	IF	SUMPRODUCTWEEKDAY	SUM+SUMPRODUCT+WEEKDAY
25	INDEX	YEARSUMPRODUCT	SUM+SUMPRODUCT+YEAR
26	INDIRECT	SUMPRODUCT	SUM+SUMPRODUCT
27	ISBLANK	COUNTIFS	COUNT+COUNTIF+COUNTIFS+IF
28	ISERR	SUMPRODUCTCOUNTIF	COUNT+COUNTIF+IF+SUM+SUMPRODUCT
29	ISERROR	SUMPRODUCTISNUMBERMATCHSEARCH	ISNUMBER+MATCH+SEARCH+SUM+SUMPRODUCT
30	ISNA	SUMPRODUCTISNAMATCH	ISNA+MATCH+SUM+SUMPRODUCT
31	ISNUMBER
32	LARGE
33	LEFT
34	LEN
35	LET
36	LOWER
37	MATCH
38	MAX
39	MID
40	MIN
41	MMULT
42	MOD
43	NOT
44	ODD
45	OFFSET
46	OR
47	POWER
48	PROPER
49	RANK
50	REPT
51	RIGHT
52	ROW
53	SEARCH
54	SEQUENCE
55	SMALL
56	SORT
57	SUBTOTAL
58	SUM
59	SUMIF
60	SUMPRODUCT
61	TEXT
62	TODAY
63	TRANSPOSE
64	UNIQUE
65	UPPER
66	LOOKUP
67	XLOOKUP
68	HLOOKUP
69	VLOOKUP
70	WEEKDAY
71	YEAR
Sheet1

Cell Formulas
Range	Formula
C2:C30	C2	=SplitText($A$2:$A$71,B2)

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

 

[Lenard]

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

 

[aRandomHelper]

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:

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

 

[Lenard]

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

 

[YasserKhalil]

@Lenard Insert a userform and add a listview control to it (This will add the reference). To enable this control, right-click on the toolbox and choose "Additional Controls" then check the option "Microsoft ListView Control"
This link may solve the problem if the last steps didn't work for you

Download Microsoft Visual Basic 6.0 Common Controls from Official Microsoft Download Center

Update for the Microsoft Visual Basic 6.0 Common Controls: mscomctl.ocx and comctl32.ocx.

www.microsoft.com

 

[Lenard]

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

 

[aRandomHelper]

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.

 

[Lenard]

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

 

[Lenard]

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