Searching for multiple instances of a string within a paragraph of text

Hi All,

I have done a search through multiple excel files using "File Locator Pro" and have exported these results as a txt and csv file (still trying a few programs to see how this can be done). What I'm trying to find is each instance in the text where a particular string appears. For example: Here is some sample text from the export file:

12,<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"><sheetPr codeName="Sheet1"/><dimension ref="A1:AF24"/><sheetViews><sheetView showGridLines="0" tabSelected="1" topLeftCell="D4" workbookViewId="0"/></sheetViews><sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/><cols><col min="1" max="1" width="19" style="1" hidden="1" customWidth="1"/><col min="2" max="2" width="19.7109375" style="1" hidden="1" customWidth="1"/><col min="3" max="3" width="12.5703125" style="1" hidden="1" customWidth="1"/><col min="4" max="4" width="24.5703125" style="1" bestFit="1" customWidth="1"/><col min="5" max="5" width="13.7109375" style="1" bestFit="1" customWidth="1"/><col min="6" max="32" width="10.7109375" style="1" customWidth="1"/><col min="33" max="16384" width="9.140625" style="1"/></cols><sheetData><row r="1" spans="1:32" x14ac:dyDescent="0.25"><c r="A1"/><c r="B1" s="1" t="str"><f ca="1">_xll.VIEW("Server1:ARMP1","!",$E$5,$E$2,$E$3,$E$4,"!","!","!")</f><v>Server1:ARMP1</v></c><c r="D1" s="5"/><c r="E1" s="5"/></row><row r="2" spans="1:32" x14ac:dyDescent="0.25"><c r="D2" s="9" t="s"><v>36</v></c><c r="E2" s="5" t="str"><f ca="1">_xll.SUBNM("Server1:SCN","","1")</f><v/></c></row><row r="3" spans="1:32" x14ac:dyDescent="0.25"><c r="D3" s="9" t="s"><v>39</v></c><c r="E3" s="5" t="str"><f ca="1">_xll.SUBNM("Server1:CC","","MotorComp1")</f><v/></c></row><row r="4" spans="1:32" x14ac:dyDescent="0.25"><c r="D4" s="9" t="s"><v>37</v></c><c r="E4" s="5" t="str"><f

Please ignore the fact that it looks like gibberish, it's the best way I could extract the strings from multiple excel files that I've found so far. I'm interested in the following:
Where ever the string "SUBNM" appears (and it appears multiple times), I'd like to include that along with the characters immediately after it up until the ) bracket. So the solution to this particular paragraph would be:

SUBNM("Server1:SCN","","1")
SUBNM("Server1:CC","","MotorComp1")

The export which I have just dumps a lot of text into either a single cell (for a csv file) or a paragraph (for a txt file). Does anyone know how I could leverage excel to return each instance of where it finds the SUBNM string in this text and include the text following it up until the ) bracket? This could appear multiple times so I'm not sure if MID will help here.

I've tried =MID(A6,FIND("SUBNM",A6,1),30)
where A6 contains the text/paragraph and then I just chose 30 characters to try to get all text that would include up to (if not past) the ) bracket - this works but it only returns the first instance of this, I need all instances.

thanks in advance.

I think:

Please Login or Register  to view this content.

Edit: sorry, had thought this was in the vba forum, not sure how to do it with formulas...

Hi Yudluger,

Thanks very much for that, it's very close to exactly what I'm looking for. Can I trouble you for two modifications?

1) I see that this looks at cell A1 for the text. How would I make it look at multiple rows in Column A (could be 50 rows of text from A1 to A50)
2) In order to analyse the output of this, how could I get the macro to dump the output to another sheet?

Thanks very much for your input, much appreciated!!

For a formula based approach, see attachment.

Please Login or Register  to view this content.

I tried the new code but I got this error message on executing the macro:

Compile Error.jpg

Pity cause I was looking forward to a flat file that I could put a pivot table on to analyse the results but thanks for the effort Yudlugar.

Sorry, I've made a mess of that. Try this:

Please Login or Register  to view this content.

Hi Yudluger,

Well this was closer... so thanks for that. I still got an error though.
Invalid procedure.jpg

I'm going to shelve this project for a while as I might have to use access to spit out a nice flat file that I can use a pivot table on - thanks to you and WHER for your solutions so far - they're much appreciated!

Muzroy,

I used the text in your first post as an example and it worked ok. It could be an issue with the data but the error message suggests it is an issue with a function or something. Do you have any broken references in your vba project? Maybe try defining the mid and instr functions as a member of strings?

Please Login or Register  to view this content.

It is better to upload a workbook which generates the error rather than a screenshot of the error message, it makes it much easier to work out the problem.