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

 

[YasserKhalil]

Maybe this because you are using Office 64 Bit.

 

[aRandomHelper]

Is there a list box control? Could be that, but I am not sure. Will take a look at it again in about 8-10 hours time.

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[aRandomHelper]

Hi Lenard, I've tried a different approach this time using a late-binding SortList (might not make any sense to you but I hope this works). Don't require any additional references for this.

Function SplitText(Keywords As Range, Data As Range) As String
    Dim c As Range, s As String, d As String, i As Long, j As Long, v
 
    With CreateObject("System.Collections.SortedList")
        For Each c In Keywords
            If .Item(Len(c.Value)) = "" Then
                .Add Len(c.Value), c.Value
            Else
                .Item(Len(c.Value)) = .Item(Len(c.Value)) & "_" & c.Value
            End If
        Next
     
        d = Data.Value
        For i = .Count - 1 To 0 Step -1
            v = Split(.GetByIndex(i), "_")
            For j = 0 To UBound(v)
                If InStr(d, v(j)) > 0 Then
                    s = s & v(j) & "+"
                    d = Replace(d, v(j), "")
                End If
            Next
        Next
     
        SplitText = ""
        If s <> "" Then SplitText = Left(s, Len(s) - 1)
    End With
End Function

Keywords are still in column A (I just didn't show in this minisheet to minimise clutter)

Book1
	B	C
1	Data	Results
2	COUNTIFSFINDLEFTRIGHT	COUNTIFS+RIGHT+FIND+LEFT
3	COUNTIFSDATE	COUNTIFS+DATE
4	COUNTIFSCOUNTIF	COUNTIFS+COUNTIF
5	COUNTIF	COUNTIF
6	SUMPRODUCTEXACT	SUMPRODUCT+EXACT
7	COUNTIFSUMPRODUCTISNUMBERMATCH	SUMPRODUCT+ISNUMBER+COUNTIF+MATCH
8	COUNTIFSUMPRODUCT	SUMPRODUCT+COUNTIF
9	MATCHISNASUMPRODUCTCOUNTACOUNTIF	SUMPRODUCT+COUNTIF+COUNTA+MATCH+ISNA
Sheet1

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

Edit:
I can foresee a potential erroneous result though, since there isn't really a 'best' approach to prioritise which Keyword to search for first.
If your data is like "COUNTIFSUMPRODUCTSUM", the code will look for SUMPRODUCT first, since that's the longest length.
Then the data gets 'trimmed' to "COUNTIFSUM" ("SUMPRODUCT" is removed).
The code then goes in a descending order in length, and when it reaches COUNTIFS, it gets trimmed again, leaving you with "UM".
So in this example the expected result was supposed to be SUMPRODUCT+COUNTIF+SUM but you will instead get SUMPRODUCT+COUNTIFS

 

[Lenard]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sure, my account details updated, thanks Fluff.

 

[Lenard]

Is there a list box control? Could be that, but I am not sure. Will take a look at it again in about 8-10 hours time.

Maybe this because you are using Office 64 Bit.

Yes, you are right, thanks

 

[Lenard]

Hi Lenard, I've tried a different approach this time using a late-binding SortList (might not make any sense to you but I hope this works). Don't require any additional references for this.

Function SplitText(Keywords As Range, Data As Range) As String
    Dim c As Range, s As String, d As String, i As Long, j As Long, v
 
    With CreateObject("System.Collections.SortedList")
        For Each c In Keywords
            If .Item(Len(c.Value)) = "" Then
                .Add Len(c.Value), c.Value
            Else
                .Item(Len(c.Value)) = .Item(Len(c.Value)) & "_" & c.Value
            End If
        Next
    
        d = Data.Value
        For i = .Count - 1 To 0 Step -1
            v = Split(.GetByIndex(i), "_")
            For j = 0 To UBound(v)
                If InStr(d, v(j)) > 0 Then
                    s = s & v(j) & "+"
                    d = Replace(d, v(j), "")
                End If
            Next
        Next
    
        SplitText = ""
        If s <> "" Then SplitText = Left(s, Len(s) - 1)
    End With
End Function

Keywords are still in column A (I just didn't show in this minisheet to minimise clutter)

Book1
	B	C
1	Data	Results
2	COUNTIFSFINDLEFTRIGHT	COUNTIFS+RIGHT+FIND+LEFT
3	COUNTIFSDATE	COUNTIFS+DATE
4	COUNTIFSCOUNTIF	COUNTIFS+COUNTIF
5	COUNTIF	COUNTIF
6	SUMPRODUCTEXACT	SUMPRODUCT+EXACT
7	COUNTIFSUMPRODUCTISNUMBERMATCH	SUMPRODUCT+ISNUMBER+COUNTIF+MATCH
8	COUNTIFSUMPRODUCT	SUMPRODUCT+COUNTIF
9	MATCHISNASUMPRODUCTCOUNTACOUNTIF	SUMPRODUCT+COUNTIF+COUNTA+MATCH+ISNA
Sheet1

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

Edit:
I can foresee a potential erroneous result though, since there isn't really a 'best' approach to prioritise which Keyword to search for first.
If your data is like "COUNTIFSUMPRODUCTSUM", the code will look for SUMPRODUCT first, since that's the longest length.
Then the data gets 'trimmed' to "COUNTIFSUM" ("SUMPRODUCT" is removed).
The code then goes in a descending order in length, and when it reaches COUNTIFS, it gets trimmed again, leaving you with "UM".
So in this example the expected result was supposed to be SUMPRODUCT+COUNTIF+SUM but you will instead get SUMPRODUCT+COUNTIFS

Hi,

Thanks for new approach.

However, SplitText UDF is not working as the late binding sortlist "System.Collections.SortedList" which need to add VBA reference "mscorlib.dll", also not found.

Regards
Lenard

 

[aRandomHelper]

Can you go to Tools -> References and list what are the checked ones? or does anything shows with a "MISSING:" in the front?

 

[Peter_SSs]

You could try this procedure.

Sub SearchWords()
  Dim a As Variant, b As Variant, c As Variant, itm As Variant
  Dim i As Long, pos As Long
  Dim s As String
  
  With Sheets("List")
    With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
      a = .Value
      .Value = Evaluate(Replace("text(len(#),""00"")&#", "#", .Address(External:=True)))
      .Sort Key1:=.Cells(1), Order1:=xlDescending, Header:=xlNo
      .TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(2, 1))
      b = .Value
      .Value = a
    End With
  End With
  With Sheets("Data")
    a = .Range("A2", Range("A" & Rows.Count).End(xlUp))
    ReDim c(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      s = vbNullString
      For Each itm In b
        pos = InStr(1, a(i, 1), itm)
        If pos > 0 Then
          s = s & "+" & Mid(a(i, 1), pos, Len(itm))
          Mid(a(i, 1), pos, Len(itm)) = String(Len(itm), ".")
          If Len(Replace(a(i, 1), ".", "")) = 0 Then Exit For
        End If
      Next itm
      c(i, 1) = Mid(s, 2)
    Next i
    .Range("B2").Resize(UBound(c)).Value = c
  End With
End Sub

My test data and results:

'List' sheet is the same as yours from post #1

Lenard.xlsm
	A	B
1	Data	Results
2	COUNTIFSFINDLEFTRIGHT	COUNTIFS+RIGHT+LEFT+FIND
3	COUNTIFSDATE	COUNTIFS+DATE
4	COUNTIFSCOUNTIF	COUNTIFS+COUNTIF
5	COUNTIF	COUNTIF
6	SUMPRODUCTEXACT	SUMPRODUCT+EXACT
7	COUNTIFSUMPRODUCTISNUMBERMATCH	SUMPRODUCT+ISNUMBER+COUNTIF+MATCH
8	COUNTIFSUMPRODUCT	SUMPRODUCT+COUNTIF
9	MATCHISNASUMPRODUCTCOUNTACOUNTIF	SUMPRODUCT+COUNTIF+COUNTA+MATCH+ISNA
Data

 

[Lenard]

Can you go to Tools -> References and list what are the checked ones? or does anything shows with a "MISSING:" in the front?

Hi,

This is what I have in VBA reference list

Thanks

Regards
Lenard