Results 1 to 9 of 9

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    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?
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  3. #3
    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?

  4. #4
    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.
    Attached Files Attached Files
    Last edited by labasta; 2011-04-03 at 10:33 AM.

  5. #5
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    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 by Simon Lloyd; 2011-04-03 at 11:09 AM. Reason: Finally tested the code after posting and amended :)
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  6. #6
    Thanks a lot Simon.

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

  7. #7
    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.
    Attached Files Attached Files

  8. #8
    Thanks efferro,

    I'll look into this now.

  9. #9
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •