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

labasta

New member
Joined
Apr 2, 2011
Messages
5
Reaction score
0
Points
0
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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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

New member
Joined
Apr 2, 2011
Messages
5
Reaction score
0
Points
0
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.
 

Attachments

  • before.xls
    13.5 KB · Views: 202
  • after.xls
    6 KB · Views: 299
Last edited:

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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:
Code:
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
 
Last edited:

labasta

New member
Joined
Apr 2, 2011
Messages
5
Reaction score
0
Points
0
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

New member
Joined
Mar 22, 2011
Messages
17
Reaction score
0
Points
0
Location
Brisbane
Website
www.edferrero.com
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.
 

Attachments

  • ParsingToKnownList.xls
    38.5 KB · Views: 621

labasta

New member
Joined
Apr 2, 2011
Messages
5
Reaction score
0
Points
0
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.:nod:
 
Top