I know regular expressions are very powerful (and echo your encouragement),
but for your example which represents the OP's stated problem, a simple
Sub tester2()
sStr = "I use ABC bcd EFGY in my job"
sstr1 = "ABC bcd EF GY"
If InStr(1, Replace(sStr, " ", ""), _
Replace(sStr1," ",""), vbTextCompare) 0 Then
MsgBox "Match made"
Else
MsgBox "No Match"
End If
End Sub
would suffice.
--
Regards,
Tom Ogilvy
"Tim Williams" <saxifrax@pacbell*dot*net wrote in message
...
Hari,
For Regular Expressions see he
http://msdn.microsoft.com/library/de...xpressions.asp
http://www.mvps.org/dmcritchie/excel...sid.htm#RegExp
You could (instead of creating all possible permutations) use a
regular expression "pattern" which could be tested against you strings
and match regardless of the existence of spaces.
For your example of "ABC BCD EF GY" you might try something like:
Sub test()
TestMatch "I use ABC bcd EFGY in my job", "ABC\s*BCD\s*EF\s*GY"
End Sub
Sub TestMatch(sIn As String, sPatt As String)
Dim regex As Object, matches As Object, m As Object
Dim x As Integer
Set regex = CreateObject("vbscript.regexp")
regex.Pattern = sPatt
regex.Global = True
regex.ignorecase = True
Set matches = regex.Execute(sIn)
If matches.Count 0 Then
For x = 0 To matches.Count - 1
Debug.Print matches(x).Value
Next x
End If
End Sub
The pattern "ABC\s*BCD\s*EF\s*GY" will match any string containing
"ABC" folllowed by zero or more "space" characters, followed by "BCD"
and then zero or more space characters etc etc. Setting "ignorecase"
to true will also match instances where the case is not the same as
your pattern.
I think this is going to prove useful to you once you have read some
more examples - the MS link has good documentation.
Good luck
Tim.
"Hari Prasadh" wrote in message
...
Hi Tim,
Im sorry, dont understand what u mean by -- Regular expressions --
Is it an excel topic (Searched excel but couldnt find it) or is it a
mathematical topic. Please tell me.
As to what I was trying to do is compare text in column A with a
base list in column I. You have previously helped me in trying to
set up a base list in the column I. Now, as I have touched upon in
some other related posts, base list is something without TYPOS.
Also, base list would have the correct number of spaces between the
words. But column A would be having typos or would be having
inconsistent spaces (Please note these inconsistent spaces cannot be
handled by Trim function etc)
I am independently trying to handle the typo though its a problem
for the long-run (time-wise).
In the present post Im handling only Space problem. For ex.
If column A has -- NetBeans --- and Column I has -- Net Beans-- then
I would like to say that the Column A and Column I are *equivalent*.
As you would see a trim function wouldnt work here.
For that what am doing is to paste the base list in Column AF and
then run a macro called MakeMeMessy (which u have seen is quite
unweildy). This macro will take each cell in Column AF and then
permute the existence and non-existence of soaces between each word
in the string and output the result to column I. Once Im able to
generate all the permutations then I run a simple Vlookup to Map the
columns.
More demonstration.
***Before running the MakeMeMessy macro . FYI - ABC , BCD, EF, GY
are separate words just like Macromedia, cold, fusion are separate
words.
Column AF Column AG
Advanced Revelations 23
Macromedia Cold fusion 34
ABC BCD EF GY 45
** After running the Macro
Column I Column J
Advanced Revelations 23
AdvancedRevelations 23
Macromedia Cold fusion 34
Macromedia Coldfusion 34
MacromediaCold fusion 34
MacromediaColdfusion 34
ABC BCD EF GY 45
ABC BCD EFGY 45
ABC BCDEF GY 45
ABCBCD EF GY 45
ABCBCD EFGY 45
ABCBCDEF GY 45
ABC BCDEFGY 45
ABCBCDEFGY 45
Thanks a lot,
Hari
India
"Tim Williams" <saxifrax@pacbell*dot*net wrote in message
...
Hari,
I would not travel any further down this route: as you can see your
code will not scale beyond only a small number of words.
Perhaps you would get better suggestions if you could restate why
you need these permutations - there will no doubt be a
betterapproach to achieve what you want than trying to generate all
possible permutations up front. For example, you might be better
served reading a little on how Regular Expressions could help you
with your task. They are ideally suited to this kind of analysis
you seem to be doing.
Regards,
Tim.