Extracting a text string from sheet VBA

[fari1]

Hi,
I've sheet, that has the data in Column A and in big text strings, i want to extract the following out of the sheet.

the text string starting from Archives and ending at index.htm and pasting it in sheet2 and same goes on for the whole sheet, whereever in the sheet it finds archives, it must copy data from there till index.htm and paste them after the used line.

e.g

 Archives/abd/data/1050915/000095012311073814/0000950123-11-073814-index.htm" href="/Archives/abc/data/1050915/000095012311073814/00009501

i want to get this, out of above lineArchives/abc/data/1050915/000095012311073814/0000950123-11-073814-index.htm

 

[fari1]

this is somehow getting converted into html, let me show u the image out of it

<b>Data</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:1790px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >125</td><td ><td nowrap="nowrap"><a href="/Archives/abc/data/1050915/000095012311073814/0000950123-11-073814-index.htm">

</a><a href="/abc/edgar/data/1050915/000095012311073814/0000950123-11-073814.txt">[text]</a></td></td></tr></table> <br />[url=http://www.excel-jeanie-html.de/index.php?f=1]Excel tables to the web - Excel Jeanie Html 4[/url]
 
i want the line
[B]Archives/abc/data/1050915/000095012311073814/0000950123-11-073814-index.htm[/B]

 

[machopicho]

Try:

=LEFT(A1,FIND(".htm",A1)+3)

or

=MID(A5,FIND("Archives",A5),FIND(".htm",A5)-FIND("Archives",A5)+4)

 

[fari1]

i've 1500 rows of data,can't use this formula over too many rows and also when i dun know in which row this word exits, its not in every row, thats why need a code for it

 

[Rick Rothstein]

This formula should do what you want...

=IF(ISNUMBER(FIND("href=",A1)),MID(LEFT(A1,FIND(">",A1,FIND("href=",A1))-2),FIND("href=",A1)+6,999),A1)

 

[fari1]

in such a big sheet, with so many rows,and where i dun know, in which row the required text string is, how can i use formula rick, i want to have a code, that would just simply loop through the whole sheet every row and bring in that text string in sheet2 column A one after the other. its because, its not just the one document, i've to parse thousands of documents.with formula, i would be getting too many blanks as well

 

[Rick Rothstein]

i've 1500 rows of data,can't use this formula over too many rows and also when i dun know in which row this word exits, its not in every row, thats why need a code for it

I think you should probably be able to use the formula I posted (it only pulls the path/filename from cells with an 'href' in it). However, if you really do want code, then where did you want the output to go? Another cell on that row (which columns) or did you want to replace the original text with the path/file name from the href? By the way, what column is your data in? And let me double check to be sure... you want the path/filename from every href in the data, right? Or are we only talking about filenames ending with htm extensions? Or are we talking about filenames of index.htm only? Once we are talking about code, we need a lot more information locked down than with formulas.

 

[fari1]

i just want to find out the text string starting from archives and ending at index.htm and want this output in sheet 2 column A after the used cells

 

[Rick Rothstein]

i just want to find out the text string starting from archives and ending at index.htm and want this output in sheet 2 column A after the used cells

Okay, so the path always starts with "archive" and the filename part is always "index.htm"... always, right?

Is your data always in a single column? If so, which column? Or can it be located anywhere within a range of columns? If that is the case, what is that range of columns?

 

[fari1]

yes the path is exactly what u mentioned and it is always in column A sheet1 and can be down as long as it could be, i dun know about the end row