The VBA code in the last few posts is a bit of a long way around of doing what can be done in a Word Dialogue box type thing using Wild things:
http://www.gmayor.com/replace_using_wildcards.htm
http://imgur.com/4Nqj1HP
The problem is getting at the Wild Things needed in the Wild Find String.
I can’t do it. But Hans and Paul did it for me here:
http://www.eileenslounge.com/viewtop...=26030#p202107
Here is my attempt at an ‘ExPlanation of the solution:
Wild Things. You make my heart sing. They make everything….. Groovy
Wild Things. You make my heart sing. They make everything….. Groovy
http://listenonrepeat.com/watch/?v=H...s_-_Wild_Thing
Wild things…. I think I hate you .. But I wanna know for sure… so
A Summary
As example. We want to remove the start and stop tags in a sample string containing a start and stop tag such as this_..
[color=Green]This is green[/color] This ‘aint matey
_.. What we finally want is this:
This is green This ‘aint matey
We use a combination of Wild things , or like a pattern to search for [ Find ] any bits like _..
[color=Green]This is green[/color]_.. and [ Replace ] that “Find”ed with
This is green
We can do, it Wildly, man, as follows:
As always the search is done from left to right
The way I do it below is probably not the most efficient way, in fact it is probably pretty stupid, but is just to demo the idea
Optional use of ( ) in the Wild thing search string
We can use brackets ( ) , optionally , to identify, ( for later use ) , the exact string parts found by the constituent Wild things in the ( ) in which they are in:
So in this example we will use a ( ) for
_ the Wild things that find the color so that the actual word color can be used again in the same wild stuff string, so as to make sure that we find in the end tag the matching word to that in the start tag.
_ Also we need a ( ) to identify the wild things that Find This is green , as we want to use that in , or rather for the complete, Replace string.
A bracket is identified by , for example, \2 if I want to reference the actual string found by the wild things we enclose in the second ( )
Build up final Wild thing Replace string, left to right, by breaking string we want up into bits. Each bit will be found by a Wild thing or things
So we are looking to break the to search for Wild [ Find ] Thing string down thus:
So I am breaking it down in to 8 bits. So I need 8 sections of wild stuff to Find those.Code:[ & ( AnythingWithout=or] ) & anything & ] & (Anything) & [\ & \1 & ]
\1 is at the 7th position and refers to what is actually found by the search for ( AnythingWithout=or] ) at position 2 ( which is bracket ( ) number 1 )
Excel starts at the left. What no literature really states clearly ( and is only obvious once you know it ) is this:
_ For most sections, Excel keeps going to the right until it Finds what the Wild thing tells it to. That is then stored, but only if that found string also has joined to it the string type in the next section’s Wild thing. Those two sections are then stored ( as well as storing separately if either of those two sections are enclosed in ( ) ), but only if that found string also has joined to it the string type in the next section’s Wild thing. That is then stored, but only if that found string also has joined to it the string type in the next section’s Wild thing. ……..etc.
A common Wild thing is * . This means anything characters and any amount. This will only include, however, anything up to if and when the next Wild thing is satisfied.
The 7th Wild thing I already have which is \1 which is referring to the 1st bit enclosed in a ( ) , which is the second bit in the broken up string , ( which is anything without a = or a ] . In our example this will be color) . Note: The number refers to the number from the left of any ( ) that I may have included, not the actual sections.
Just to make that last point clear, and to demonstrate another, I will do this_..
_.. so my Replace is then \3 . I do not need the second ( ___ ) , which I could reference by \2, ( and it can help to result in an “expression too complex” error ! ).Code:[ & ( AnthingWithout=or] ) & ( Anything & ] ) & (Anything) & [/ & \1 & ]
I put the second ( ___ ) in also to demo that a ( __ ) can include more than one wild bit
So here is one example Find sting with Wild things that “works” ( I added spaces to show better the 8 sections, but in use those spaces must be removed ). There are three optional ( ) sections. The second ( ) section encloses two Wild thing sections: Those are the third and forth counting from the left.
This is the actual Wild Find stringCode:[\[] ([!\]=]@) (* \]) (*) \[/ (\1) \]
___ [\[]([!=\]]@)(*\])(*)\[/(\1)\]
To '_- ExPlain the 8 parts:
Part 1 [\[]
Excel start “looking” from the left to find a [
As a general rule Wild things are enclosed in a [ ] pair to identify them as such. So a [ ] is a Wild thing. They do not always have to be enclosed in a [ ], as is the case here. Here we are effectively looking to Find a single simple character, [.
( In the final Find string, only the second Wild section needs to be enclosed partly in a [ ] pair ).
So this section written as \[ instead is just as good. I suspect my use of the extra [ ] here does not have any bad effects, as it might be that the final string would be at compile or whatever reduced to \[ in either use of \[ or [\[]
We need to use the Wild thing, \ , quite a bit , in our total string for the same reason that we do here: This is because the \ has another use in Wild things in addition to the already discussed way like \7 ( where 7 would be for the 7th ( ) ). This other use of \ is to allow us to use characters as simple text which have a specific use in Wild things, so that Excel knows that we want to use them as their literal text string, rather than as Wild thing. Here it is needed for the first [ which we want to find.
( It follows that if we were looking for a \, then we would need to use \\ )
In our example string we find with this first Wild thing section the first [
Part 2 ([!\]=]@)
As noted the ( ) allows the string actually found in this Wild bit to be used later to the right via the reference \1.
As for this:
[!\]=]@ ( which could just as well be written [!=\]]@ )
We need a [ ] pair here, as within that is part of the Wild instructions for what to search for , and that has a further Wild instruction, @ , applied to that.
So in such a [ ] pair we would normally expect some specific wild stuff
Here the Wild thing, ! , means we search for any character other than those after included up to the closing tag ]. In our sample string , our first Wild bit found the first [ which now has added on any string at the next character if it is not a = or a ]
So this part of the second wild bit will find c in our sample string. If we had no other wild instructions here, then Excel would be content/ satisfied with that c. That would be joined on to the first [ found, so our result so far would be [c . In effect Excel stops going to the right when it is satisfied, and goes on to the next Wild bit. But we have another wild instruction here: The @ instructs to look for any amount of characters meeting the conditions before it, in this case the conditions in the [ ]. So the “Find” will stop finding characters when it no longer has a not = or a not ]. In our sample string the characters meeting the conditions are tacked on to a [ so will be will be [color. What [!\]=]@ has actually found and what the brackets ([!\]=]@) can be thought of as “holding” is color.
An alternative for @ would be {1,} which looks for between 1 and any amount of what is before it.
If we had used {1,4} or {2,4} instead of @ we would have so far for the complete found string [colo
If we had used {5,5} we would not have found a matching code tag word with less than 5 characters.
Parts 3 and 4 (*\])
This is unnecessarily in ( ) . *\] will do
The literature says * looks for any amount of anything. I think that is not strictly correct, or at least, explains it badly. It would appear to look for any amount of anything only until the next wild condition is met. That is why I chose to put it in ( ) just to demo the dependence, as it were. But this (*)(\]) or this *\] would work just as well (*)(\]).
Once again the \ is required for telling Excel that we want it to recognise ] as a string rather than a specific Wild thing. For this section , with my example string, I get from this complete 2 part Wild section
=green]
If our code tags were of the form like: [b]SomeBoldText[/b] , then the search would have “stopped before it started”, as it were, as the ] would have “stopped” it
The combination of the 2 part Wild sections in this case would return just the single text character ]
Parts 5 and 6 (*)\[/
I contradict what I said in the last section slightly by using a ( ) around the * . Once again the * does nothing useful without the next wild bit to “stop” it. But what it does Find before it “stops” is what we actually need for Replace string. Hence I need a ( ) to reference what it Finds. This total Part 5 and part 6 section returns in our example
This is green[/
and the part referenced inside the ( ) is
This is green
Part 7 (\1)
As discussed already here the Wild thing, \1 , instructs to Find a string equal to that actual text found in the first ( ) if it is just before the next to be found ( which we will see in the next Part is the last character pair found, [/ ). The actual string found by [!\]=]@ in Part 2 , was , in our example the text color
Part 8 \]
Finally only a complete final Find attempt is successful if the last character is a ] , that is to say if the character after the text, in our example, the second color, is a ]
_...
The Final “Find”ed String for our example would be
[ & color & =Green & ] & This is green & [/ & color & ]
Reply With Quote
Bookmarks