try this code
Sub tests()
Dim i As Long
Dim z As String
For i = 1 To Sheets(1).Range("a65356").End(xlUp).Row
If InStr(1, Application.WorksheetFunction.Trim(Cells(i, 1)), " ") > 0 Then
Cells(i, 2) =
Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Trim(Cells(i,
1)), " ", "|")
End If
Next i
For i = 1 To Sheets(1).Range("a65356").End(xlUp).Row
z = ""
If InStr(1, Application.WorksheetFunction.Trim(Cells(i, 1)), " ") = 0 Then
For swa = 1 To Sheets(1).Range("a65356").End(xlUp).Row
If swa <> i Then
If InStr(1, Application.WorksheetFunction.Trim(Cells(swa, 1)),
Application.WorksheetFunction.Trim(Cells(i, 1))) > 0 Then
z = z & Application.WorksheetFunction.Trim(Cells(swa, 1)) & "|"
End If
End If
Next swa
If Right(z, 1) = "|" Then
Cells(i, 2) = Left(z, Len(z) - 1)
Else
Cells(i, 2) = z
End If
End If
Next i
End Sub
On Wed, May 11, 2011 at 6:03 PM, Ivaylo <ivanov.iva...@gmail.com> wrote:
> Hi to all VBA gurus,
>
> I need help with an Excel macro which can do the following:
>
> I have a long list of dictionary headwords in column A (sorted
> alphabetically), something like this:
>
> abnormal termination
> abnormal
> abort
> about
> absolute
> absolute address
> absolute code
> accent
> access
> access address
> access mode
> access control
> access time
> address
>
> ...
>
> The list consists of single words and multiword phrases.
>
> My task is to put in column B reference to the single words that
> constitute the multiword phrases. For example, for "abnormal
> termination" column B must be populated with "abnormal" and
> "termination", separated by "|":
> abnormal termination abnormal|termination
>
> The second part of this task is that for the single-word headwords I
> must put references to their corresponding multiword phrases. For
> example, for "absolute" column B must be populated with "absolute
> address" and "absolute code", separated by "|":
> absolute absolute address|absolute code
>
>
> Here is a desired output after the processing:
>
>
> Column A - Column B
> abnormal - termination abnormal|termination
> abnormal - abnormal termination
> abort -
> about -
> absolute - absolute address|absolute code
> absolute address - absolute|address
> absolute code - absolute|code
> accent -
> access access - address|access mode|access control|access time
> access address - access|address
> access mode - access|mode
> access control - access|control
> access time - access|time
> address - absolute address|access address
>
> How to handle exceptions:
>
> If the consituent word doesn't have its own headword in the list, then
> the missing word must be copied in Sheet 2, column A and the phrase
> from which it comes from in column B - i.e. Sheet 2 must be list all
> the words which are part of a multiword phrases but don't have their
> own headwords.
>
> This is the case with "abnormal termination". The word "abnormal"
> exists as a headword, and the phrase "abnormal termination" is copied
> in column B next to abnormal. However, the word "termination" doesn't
> exist in column A (as a headword), then "termination" must be copied
> to column A in Sheet 2 and the phrase "abnormal termination" to column
> B in Sheet 2 (next to "termination").
>
> I'll be very grateful if anyone can help me writing such a macro.
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com <http://akoul.blogspot.com/>
*akoul*.wordpress.com <http://akoul.wordpress.com/>
My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
P Before printing, think about the environment.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
