A repost - Permuting SPACES within a String of words

  • Thread starter Thread starter Hari Prasadh
  • Start date Start date
H

Hari Prasadh

Hi,

I posted this problem around 3-4 days back.....I have tried to paraphrase
the problem in a probably simpler way below.

I want to generate permutations of a string made up of 2 or more words in
such a way that I get outputs :

a) where there is no space between any of the words in the string
b) Where there is only one space between any 2 words in the string.
c) where there is a single space between some of the letters in the string
but rest words dont have any space between them. And do this process for all
possible permutations.

I have got solutions to a) and b) by swiping from previous posts. ( Split
array method )

It's the c) which is..

For example if I have a string in Cells(1,1) having the value -- I went to
Paris --

the answer for a) will be -- IwenttoParis --
the answer for b) will be -- I went to Paris -- Basically, b) will be same
as the original string.

the answer(s) for c) would be
i) Iwentto Paris
ii) I wenttoParis
iii) Iwent toParis
iv) Iwent to Paris
v) I went toParis
vi) I wentto Paris

Explanation - of part c) :
In parts i) and ii) 3 of the words in the string have been combined together
and the lone word is separated by a single space.

In part iii) 2 sets of adjacent words have been combined together and there
is a single space between these 2 set.

In parts iv), v) and vi) ONLY one set of adjacent words have been combined
together and there is a single space between any 2 words.

How to effect the above combinations... in view of the fact that number of
words in the string (Cells(1,1)) would be variable.

Thanks a lot,
Hari
India
 
You should use the split function to separate the words.
array=split(string," ")
Then you should check for empty cells array
aray(n)=""
Use ubound to find out how many cells your array contains
Then you can construc your permutations by concatanating the words
newstring= array(0) & " " & array(1) & ............
You should be able to do this by some looping codes

Ole Michelsen
 
Hi Ole,

I learnt the Split array method recently from NG. What you are suggesting
will help me in getting solutions to a) and b) (which NG has provided to me)

But my problem is with part c).
How to construct Logic which would give me the following :-
newstring = array(lbound) & " " & array(1) & "" & array(2) & "" & array(3) &
" " .....& array(ubound)

That is in the above case some words have spaces (single space) between them
while some dont. How to generate the complete set of permutations based on
this.

Thanks a lot,
Hari
India
 
Hi,
I have written an inefficient code (below) of accomplishing what I want
to do. Could anybody help me in shortening it up. Also, presently I
have considered only till 5 words in the original string. For 6 word
combination I will have to write down 32 lines of code more (rather
32*3) and so on. Can this code be made Dynamic.?

Option Explicit

Sub MakeMeMessy()
Dim i As Integer
Dim lastRow As Integer

Range("i2:j65536").Select
Selection.ClearContents

lastRow = Range("AF65536").End(xlUp).Row
Cells(2, "i").Select
For i = 2 To lastRow

Splitwords ActiveSheet.Cells(i, "af").Value

Select Case UBound(arrWords) - LBound(arrWords)

Case 0
ActiveCell.Value = arrWords(LBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
Case 1
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
Case 2
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
Case 3
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
" " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) & " " &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
" " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) & " " &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) &
arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
Case 4
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
" " & arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
" " & arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) & " " &
arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
" " & arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) & " " &
arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
" " & arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) & " " &
arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) & " " &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & " " & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) & " " &
arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & " " & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = arrWords(LBound(arrWords)) &
arrWords(LBound(arrWords) + 1) & arrWords(LBound(arrWords) + 2) &
arrWords(LBound(arrWords) + 3) & arrWords(UBound(arrWords))
ActiveCell.Offset(0, 1).Range("A1").Value = Cells(i,
"ag").Value
ActiveCell.Offset(1, 0).Range("A1").Select
End Select

Next i


End Sub


Public Sub Splitwords(sText As String)

Dim x As Integer
Dim arrReplace As Variant

arrReplace = Array(vbTab, ":", ";", ".", ",", "-", "/", "\",
Chr(10), Chr(13))
For x = LBound(arrReplace) To UBound(arrReplace)
sText = Replace(sText, arrReplace(x), " ")
Next x

arrWords = Split(Application.WorksheetFunction.Trim(sText), " ")

End Sub

Regards,
Hari
India
 
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.
 
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
 
Hari,

For Regular Expressions see here:
http://msdn.microsoft.com/library/d...-us/script56/html/reconregularexpressions.asp
http://www.mvps.org/dmcritchie/excel/grove_digitsid.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.
 
Hi Tim,

Thnx a lot for your kind help. Will go through it. (Have been running in
circles till now)

Thanks a lot,
Hari
India
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Permutations andText to column problem 6
Permuting compound words - With or Without SPACES 2
Removing extra spaces in a string 3
Count and get position of spaces within a string 4
Generating multiple variable permutations 2
Phrase Permutations 1
Userform textbox string to header causes unwanted double spacing 4
Removing words from a string? 4

Back
Top