copying specific text from one cell and pasting in the next column, same row

[labasta]

I've been emailing this around a few excel add-in sellers on the net, but I'm not sure it can be done. If not excel, would another programme do it? I know Regex language could do it.

I'm willing to pay for this customization if it can be done. Here is the email I sent around:


What I need is software to copy (not extract) text from excel cells and add
this text to another cell (the next column along). I need to be able to
enter the words which the software is to copy and paste.

Example:

*dog training is good
*cat was walking down the street
*parrot made a sound and ran away
*hamster ran away
*mouse made a squeaking sound
*gerbil ran under the floor without making a sound


Let's say I have the above text in each cell in excel.

Let's say I want to copy and paste the following words:

ran
sound

Excel then pastes these words in the next column in the same row in the same
order it finds them. It would do this:

*dog training is good
*cat was walking down the street
*parrot made a sound and ran away sound ran
*hamster ran away ran
*mouse made a squeaking sound sound
*gerbil ran under the floor without making a sound ran sound


Do you see what I mean?

Can this be done?

[Simon Lloyd]

Its simple to do but i don't understand your logic from your example of which rows get the additions of which words, some contain RAN and SOUND but then some only contain one of them?

Can you zip and attach a sample workbook here so we can see a before and after look?

[Bob Phillips]

Are you saying that if a cell doesn't contain the text you input, it copies it as is. If it does contain that text, it appends your input to the existing value?

Seems odd, but trivial.

Or are you asking for customisation on existing products?

[labasta]

Thanks for getting back so soon.

I have a list of urls and I basically want to extract 3 or more keywords from the url.
I've given the example of a property website.

Attached is the excel sheet of before and after. (Office XP) It's .xls, but I'll be working in .xml. Not sure if that matters.

I would have to choose my own keywords which are to be extracted, e.g. house for sale, offers to buy bungalow, etc.

[Simon Lloyd]

I've not looked at your examples (no time at the mo) but just dashed this off, it should work for what you seem to require:

Sub Extract_Words()
Dim rng As Range, MyCell As Range
Dim IB As String, INo As Long, i As Long, fVal As Long
INo = Application.InputBox("Enter Number of words to find", "Word Count")
For i = 1 To INo
    IB = Application.InputBox("Enter word number " & i, "Word Finder")
    Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        For Each MyCell In rng
        On Error Resume Next
            fVal = Application.WorksheetFunction.Find(IB, MyCell.Value, 1)
            If Mid(MyCell.Value, fVal, Len(IB)) = IB Then
            MyCell.Offset(0, 1) = MyCell.Offset(0, 1).Value & " " & IB
            End If
        Next MyCell
Next i
End Sub

[labasta]

Thanks a lot Simon.

I'll play with this later today. I'm a noob with programming though, so I'll probably be back.

[eferrero]

Sounds like you want to look for strings contained in a known list, within a list of data. See if the attached worksheet does what you want.

[labasta]

Thanks efferro,

I'll look into this now.

[labasta]

eferrero,

That script is fantastic. Thanks a million. It is just what I am looking for.

I never got to try Simon's script after all yesterday as I didn't have time. Thanks anyway Simon for your effort.

Very quick responses.

Thanks again.