Copy lines from multi-line text across multiple rows.

I'm working on complex research into router configuration. I have created many macros to do all kinds of useful things.
However, I'm stumped even how to ask this question completely. (which is why I'm here now)
I have a column of cells that contain multiple lines in each cell. I need to extract every line that starts with 'interface' and ends with chr(10).
Each cell will be similar to the following (with 2 to 5 matches per cell):
<text example - multiline in one cell>
!
interface GigabitEthernet0/2.573000
description ***text to describe***
***more router code***
!
interface GigabitEthernet0/2.573099
description ***text to describe***
***more router code***
!
interface GigabitEthernet0/2.573100
description ***text to describe***
***more router code***
</text example>


So, what I would get for the above, would be text in a new column. It would be great if the cell in the new column matched the row it was from/on. So, if the above text was in A1, some other column (say Z) would have the matching extracted lines. As an added bonus, I'd like to stick 'no ' at the beginning of each individual line of every new cell.
If I did that, the final result in the new cell on the same row would be:
<text example - multiline in new cell>
no interface GigabitEthernet0/2.573000
no interface GigabitEthernet0/2.573099
no interface GigabitEthernet0/2.573100
</text example>

From this idea, I will be writing router cleanup scripts based on all kinds of junk I need to clean up in a router once desired data has been copied to a new router (ip routes, prefix lists, etc).

Thank you all!

Originally Posted by Norie

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Interestong... FireFox totally died trying to upload. I had to switch to M$Nagrivator...
Column B has some cleansed data. Column B needs to be transmorgrified into column K as shown (done manually here). I'd like to do the other columns, but a module to do the first would be easy to modify.

Thank you so much for having more brains than me (and are willing to share).

@jindon - thanks. that will put the 'no ' in, but I also need to do this across multiple rows where each cell may have the 'interface' line in multiple lines within each cell. but, I only need all interface lines but none of the lines after it (within the same cell).

Your data

!
interface GigabitEthernet0/2.###1
description |1234|_descriptive text
!
interface GigabitEthernet0/2.###2
description |1234|_descriptive text_1
ip vrf ...
ip address ...
service-policy output ...
!
interface GigabitEthernet0/2.###3
description |1234|_descriptive text_2
ip vrf ...
ip address ...
service-policy output ...
!
interface GigabitEthernet0/2.###4
description |1234|_descriptive text_3
ip address ...
!"

Result

no interface GigabitEthernet0/2.###1
no interface GigabitEthernet0/2.###2
no interface GigabitEthernet0/2.###3
no interface GigabitEthernet0/2.###4

Something wrong with the result?

I'm sorry, you're correct. I will test this Monday AM and reply with results. I will merge that into a macro that I will launch through a control. I'm done for the weekend and will look at it Monday. Thank you so much. I will be happy to share with you what I've done for all of my project.

OK,

Hope you can coop with the function.

@jindon I apologise. Your function seemed so short, I couldn't imagine that it would do everything. I am humbled. It works. Could you explain how?
I will use the function in a macro and will need to do to copy/paste values to get the actual text, not a formula result. Again, wow! Thank you.

Not sure how you want it.

Output in Col.K

Please Login or Register  to view this content.

This is perfect. I was able to use that as a core to do similar updates to other columns in my spread sheet. I'm now cleaning up a lot of my macros, trying to get rid of some terrible inefficient code. I am so bad at this, but at least it works. :P

@Jindon - I know this will look ridiculous compared to what you know... but, here goes. Based on the excellent function you showed, I did the following. I would haved liked to have made the repeating bit a call to a sub routine, but the double-quotes stumped me, so I just did it brute force.

Please Login or Register  to view this content.

Can be like this

Please Login or Register  to view this content.

@Jindon - I think you are more than guru. Is uberguru a word?

Thanks for the feed back and uberguru is not in my dictionary.

If you are happy with the solution, mark the thread as "Solved".

I want to post the complete solution to what I did with your valuable help, so the next guy might stumble on this and be amazed by your great mind.
So... the original idea is to extract one (or more) line(s) of text that match a string and copy those lines to a new column and ad 'no ' at the beginning.
As a bonus, do this to multiple columns of different multi-line text cell that each contain a line (or more) of different unique values that need to go into new columns.
jindon's EXCELlent solution deals with contiguous and non-contiguous columns.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I can't see how to mark this solved. I feel dumb. :P

http://www.excelforum.com/faq.php

will tell you how.

The faq didn't exactly match what I had to do, but it did show me where to look. Again, thank you so much.

I'm curious how that function does its magic. And, I'm curious about a modification...

The function does an excellent job of giving me the needed 'no interface...' lines, but I'm curious how to modify that function to instead add a string after the text.
In this case, instead of adding 'no ' in front of the extracted line, I'd like to instead add 'chr(10) & "shutdown" at the end of each line.
That way, I'd get:
"interface ..."
"shutdown"
for each original
"interface..." line.

Could you guide me in how the function works, so that I might puzzle out my answer?

Add one more argument to Function.

=StrFilter(B2,"interface","",CHAR(10)&"shutdown")

Please Login or Register  to view this content.

I tried to add code to use that, but I'm not doing something right.
I added a 2 to the name of this function so as not to interfere with the original code.
When I try this, I get #VALUE! in column O.

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.

Originally Posted by Rhudi

I added a 2 to the name of this function so as not to interfere with the original code.

You don't actually need to have 2 functions.
The function has 4 arguments now.

3rd for prefix and the 4th for suffix.

So

Please Login or Register  to view this content.

will give you the first result and

Please Login or Register  to view this content.

will give you both prefix and suffix added results.

I thought that was what it looked like, but I was scared to try. But, I combined the functions into the one-improved function (as you pointed out) and it works beautifully.
I would love to post the template somewhere as an example, but don't know how useful it would be without data. Unfortunately, all the data I have is proprietary.

If you ever visit South Carolina, I will have to buy you a dinner.

one more little question... :P
How easy could this be modified to also match lines that end with specified text?
ie, a line might have "neighbor 10.248.254.5 activate" and the match is " activate" at the end of the line.
So, "neighbor 10.248.254.5 activate" would become "no neighbor 10.248.254.5 activate".

Again perfection. I was able to wrap a similar VB Macro structure around that new function and it works exactly as expected. How do you know this stuff? :O

I hate to ask a new, but parallel question to this... but, here goes. :P
In my original post, I asked how to search a cell with multiple lines of text for every instance of "interface ***" and getting a new cell that contains "interface ***" & char(10) & "shutdown" (matching the *** with whatever was there).
Now, I have (to me) a much more difficult question... each line that has "shutdown ***" will be followed by a line that says "description *****". So, what I need to do now is... after the added 'shutdown', add the original trailing 'description ****' text and adding a variable-set string after that. How easy?

Can you show me some examples of befrore/after thingy?

Ok, the examples... 1-Original text, 2-Earlier help output, 3-Current request output. For every 'interface...' line, the next line in the cell will be a 'description'. I need that description added to a new line after the original code/help on the shutdown line, with a string I will set in a variable appended to that copied description.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Assuming the original string in A1

=myInsert(A1,"interface","shutdown","_VARIABLE_DEFINED_STRING")

Please Login or Register  to view this content.