http://www.sakkal.com http://www.jannah.org MS-Excel Formula to covert flat text URL into clickable URLs ************************************************************ ************************************************************ Step1: ======= All URLs should be in column A1 of excel sheet and sorted (no blank cells in this column) Step2: ======= Stand in lets say B1-cell and paste this formula, then drag the cell corner to the corresponding A-column’s last non-empty cell. =CONCATENATE("

", A1,"

") Step3: ======= Create a blank text file with notepad and paste following text in it Step4: ======= select all text from column "B", copy it and paste between the above code where empty place is visible as shown above i.e. any line between Tags. Step4: ======= Save the file with any file name but file type as html by setting "save-as-type" to "All". From the file name remove .txt suffix and change it to .html FOR extracting URLs/Site-Addresses from Opera Win file ******************************************************* ******************************************************* A) Before Step-1, open the .win file in notepad and copy all text B) Then paste all text standing in Excel A-Column's top most cell i.e. A1-cell. C) Remove the 0= 1= and similar garbage text from the start of each URL, with this formula.... Stand in B1-cell and paste this formula, then drag the cell corner to the corresponding A-column’s last non-empty cell. =IF(ISERROR(MID(A1,SEARCH("http",A1),LEN(A1)-SEARCH("http",A1))),"",MID(A1,SEARCH("http",A1),1+LEN(A1)-SEARCH("http",A1))) Remember Do NOT use Find-Replace method for this, it will replace the text from the URL address as well. Example 0=http://cards.123greetings.com/cgi-bin/newcards/showthumbs.pl?q1=edec_ramadan&log=eid101 Here the word ... "q1=edec_ramadan" has the text 1= D) The formula column i.e. B-Column will now be showing only the URLs and for every non URL text in A-Column, the corresponding cell in B-column will be empty. E) Select full column-B and Copy it. Then in a separate sheet standing in cell A1, do a paste-special (Edit --> Paste Special) and select values (third option), Press OK. F) Sort the column either ascending (Ascending is good because it will show the group internal sorted proper Alphabetically but the group will appear in extreme Bottom of worksheet) or descending (Though Descending will immediately pull all the text in top of worksheet but the group internally will be sorted Alphabetically-descending, You can select the text portion again and sort it ascending), Whichever u do, the purpose is to collect all scattered cells together as a group so this procedure will group all the URLs G) Delete all the empty rows above and from below that cluster/group of URLs by selecting the blank cells and press "Ctrl -" press ok then OR just copy the group by selecting the text only and paste in a new sheet in which do the paste operation while standing Cell "A1" (extreme top left) H) Go to Step-1 and follow the steps to last i.e. step-4 Enjoy !!! By: Muhammad Imran (Islamabad, Pakistan) i-m-r-a-n-1@gmail.com (without dashes i.e. imran1). to avoid spam search engines