Substring Search !

Eddny

New Member
Joined
Jun 26, 2018
Messages
26
Hello!

So let's say I have:

Dim MainString as string, Substring as string
Dim Result as Boolean

MainString ="ABCDE|ABCDF|CGHMN|QRSTU|CJKLM"

Question: Is there an easier (faster) way to check whether MainString contains all elements of Substring without using loops?

Example 1: If Substring ="QRSTU|ABCDE|CGHMN" then Result = True since all elements of Substring are found in MainString.

Example 2: If Substring ="ABCDF|QRSTU|HPXYZ" then Result = False since not all elements of Substring are found in MainString.

Is there a way to perform these checks to see whether all the elements in Substring exist in MainString without using loops? I have thought about the Instr function, the Like operator, etc, However, it appears in each case I will have to use a loop.
Any help would be appreciated.

Eddny
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
Whats the problem with the loop?? As you are going to have to split your substring up then you have to use one.
 

Eddny

New Member
Joined
Jun 26, 2018
Messages
26
Whats the problem with the loop?? As you are going to have to split your substring up then you have to use one.
Steve - Yeah, thanks! I tried the loop route (where I first use Split() on the sub-string and loop to check each element's existence in MainString using Instr(). It works ok but need something faster, if possible. Have to do this repeatedly for a huge data set.

I was also wondering if there was a way to use the Like operator in a single-step check with some combination of a wildcard "*" search.
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
Not as i can see. You will have no choice but to split the substring but then you need to test each part hence need for a loop. The loop wont be slow by the way.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
See if this non-looping method works for you.
Code:
Sub CheckAllSubstrings()
  Dim MainString As String, Substring As String
  Dim Result As Boolean
  Dim RX As Object

  MainString = "ABCDE|ABCDF|CGHMN|QRSTU|CJKLM"
  Substring = "QRSTU|ABCDE|CGHMN"
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = MainString
  Result = Replace(RX.Replace(Substring, ""), "|", "") = vbNullString
End Sub
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
Ive never used those expressions much. I presume looking at that it understands that the pipe is a seperator?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
Ive never used those expressions much. I presume looking at that it understands that the pipe is a seperator?
If that question is directed to me, then in a regular expression "pattern" line, a pipe acts as "or".
 

Eddny

New Member
Joined
Jun 26, 2018
Messages
26
See if this non-looping method works for you.
Code:
Sub CheckAllSubstrings()
  Dim MainString As String, Substring As String
  Dim Result As Boolean
  Dim RX As Object

  MainString = "ABCDE|ABCDF|CGHMN|QRSTU|CJKLM"
  Substring = "QRSTU|ABCDE|CGHMN"
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = MainString
  Result = Replace(RX.Replace(Substring, ""), "|", "") = vbNullString
End Sub
Peter - I don't have experience with regex but will try your code and let you know. I hope I don't have to activate anything else before using it. Thanks!
 

Eddny

New Member
Joined
Jun 26, 2018
Messages
26
Ive never used those expressions much. I presume looking at that it understands that the pipe is a seperator?
Steve - If your question was for me regarding the original post then, yes, the pipe "|" is being used as a delimiter or separator in my strings.

Thanks!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
... will try your code and let you know.
I did assume that all sections of your substrings are 5 characters, like your samples. If that is not the case and/or there are other differences (eg upper/lower case or different delimiters) then more information and examples would help.
 

Forum statistics

Threads
1,082,295
Messages
5,364,359
Members
400,792
Latest member
Dxmiian

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top