+ Reply to Thread
Results 1 to 4 of 4

macro - search within a cell and edit the text string

  1. #1
    Registered User
    Join Date
    07-21-2011
    Location
    Bratislava, Slovakia
    MS-Off Ver
    Excel 2003
    Posts
    2

    macro - search within a cell and edit the text string

    Hello, I have this problem:
    I need to search the fourth occurence of the character / within each cell of a database of web links and delete all characters that come after it.

    the links look like this:
    http://www.parfums.cz/banana-republi...toaletni-voda/
    http://www.parfums.cz/benetton/benet...toaletni-voda/
    http://www.parfums.cz/bugatti/bugatt...toaletni-voda/
    http://www.parfums.cz/dsquared2/dsqu...toaletni-voda/
    http://www.parfums.cz/la-perla/la-pe...toaletni-voda/
    http://www.parfums.cz/mauboussin/mau...femovana-voda/
    http://www.parfums.cz/naomi-campbell...toaletni-voda/

    i need them to look like this:
    http://www.parfums.cz/banana-republic/
    http://www.parfums.cz/benetton/
    http://www.parfums.cz/bugatti/
    http://www.parfums.cz/dsquared2/
    http://www.parfums.cz/la-perla/
    http://www.parfums.cz/mauboussin/
    http://www.parfums.cz/naomi-campbell/

    Thank you in advance for your help
    Filip

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,124

    Re: macro - search within a cell and edit the text string

    Formula

    =LEFT(A1,FIND("^",SUBSTITUTE(A1,"/","^",4)))

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,124

    Re: macro - search within a cell and edit the text string

    Formula

    =LEFT(A1,FIND("^",SUBSTITUTE(A1,"/","^",4)))

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-21-2011
    Location
    Bratislava, Slovakia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: macro - search within a cell and edit the text string

    well, I solved it without macros with this formula:

    =LEFT(C56;FIND("/";C56;30))
    where C56 is a cell with original link and 30 is a number approximately between the 3rd and 4th char /

    But it creates a new table with the final links. I would like to have it on the same place, where the original links were and have a mean that would be lenght-proof
    (there is also domain http://aoro.ro/ which is much shorter and some much longer as well)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1