1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to mass edit a bunch of url's in excel

Discussion in 'BlackHat Lounge' started by freckletone, Aug 22, 2011.

  1. freckletone

    freckletone Regular Member

    Joined:
    Jul 22, 2009
    Messages:
    373
    Likes Received:
    55
  2. yogan

    yogan Newbie

    Joined:
    Jun 13, 2011
    Messages:
    21
    Likes Received:
    5
    Location:
    RU
    may be 1st- autochange *.com/ to *.com?/ than using some tool remove everything after "?" (don't know in excel )but can be done in notepad++ or some other specific software like xrumer
     
  3. LilithX

    LilithX Registered Member

    Joined:
    Oct 4, 2010
    Messages:
    60
    Likes Received:
    25
    Occupation:
    Internet Marketing | Graphic Designer
    Location:
    USA
    So you want to delete everything after the / ?
     
  4. d2ugsd

    d2ugsd Registered Member Premium Member

    Joined:
    Mar 16, 2008
    Messages:
    81
    Likes Received:
    36
    Copy all the urls into a text file, click Ctrl. + H, on the find section enter .com/ , on the replace section leave black, replace all the urls.
     
  5. freckletone

    freckletone Regular Member

    Joined:
    Jul 22, 2009
    Messages:
    373
    Likes Received:
    55

    yes... but all the url's are different

    all different urls on different domains

    only want homepage

    I will try the copying in notepad in a text file way right now...
     
    Last edited: Aug 22, 2011
  6. freckletone

    freckletone Regular Member

    Joined:
    Jul 22, 2009
    Messages:
    373
    Likes Received:
    55

    I just tried it in excel and it doesn't work...

    It got rid of the .com part

    I want the .com part on there. I wan tit to show the domain name like

    http://www.domain.com

    now it's showing http://www.domain.com/blach.html
     
  7. dcuthbert

    dcuthbert Regular Member

    Joined:
    Jun 15, 2011
    Messages:
    411
    Likes Received:
    249
    If you have Scrapebox, you can import the list and trim to root, then export.

    In Excel:
    1. Use find+replace, replace http:// with "" (eg blank) - the reason is, this will mess with trimming to root, we can add it later.
    2. Select the whole column, and select "Text to Columns" from the Tools menu (or Data tab in 07/10)
    3. Select Delimited and click next
    4. In the Other box, enter "/" without the quotes, and untick whatever is selected by default, then click finish.

    Excel will now have your root domain in column A, and all other parts of the URL in columns B onwards, so you can just delete all columns except for A.

    To add back in the http:// - once you've deleted the other columns, go to cell B1 and type =CONCATENATE("http://",A1) and hit enter. Copy the formula down all rows, then copy & paste special as values. Delete column A.

    All done! Let me know if you need any help.
     
  8. zachtan

    zachtan Newbie

    Joined:
    Oct 11, 2010
    Messages:
    17
    Likes Received:
    4
    This is what I'd do

    Using excel search and replace function (Ctrl +f), replace all "//" with "*".
    hxxp://vvvvvv.randomurl1,com/blah.jhtml becomes hxxp:*vvvvvv.randomurl1,com/blah.jhtml

    Then replace all "/*" with ""
    hxxp:*vvvvvv.randomurl1,com/blah.jhtml becomes
    hxxp:*vvvvvv.randomurl1,com[/url]

    Then replace all "*" with "//"
    hxxp:*vvvvvv.randomurl1,com becomes
    hxxp://vvvvvv.randomurl1,com
     
  9. freckletone

    freckletone Regular Member

    Joined:
    Jul 22, 2009
    Messages:
    373
    Likes Received:
    55

    This worked for me! Blackhat people are smart and know everything! Thank you a million! I didn't do the last part the "applying formula to the entire column" because it turns out it was ok to end up with just domain.com
    without the http.

    You saved me so much work. Thank you!