Word VBA Replace multiple Spaces in Text with BB Code String

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Word VBA Replace multiple Spaces in Text with BB Code String

Post by Doc.AElstein »

Word VBA Replace multiple sequential Spaces in Text with BB Code Strings
Hi,

_ I am Using Office 2007 and 2010 just now. I have about a year of Excel VBA knowledge but only done a few Word codes with the macro recorder.
_ I am stuck on a few bits. No rush on this, but if anyone happen to know the answer to the bits I am stuck on sometime I would be grateful.
_ I am trying to get a code to replace spaces of more than two spaces in a text with a piece of BB Code
_ I have got a way to do most of what I want. It involves 3 codes mostly got from the macro recorder, along with a few steps I still need to do manually in between and before I start the first code.

An Example to explain better:
_ I would like all the following to be done with one single code if possible.

_ My start point before running the code is to have a Word document open with a text looking something like this selected:

Code: Select all

110      If cell.Offset(0, 6).Value < cell.Value Then
120             cell.Offset(0, 6).Value = cell.Value
_ I then want a modified version of that copied to the clipboard. The modified version as it would look like if I pasted from the clipboard after the code ends should look like this:

Code: Select all

110[color=#BFFFFF]~~~~~~[/color]If cell.Offset(0, 6).Value < cell.Value Then
120[color=#BFFFFF]~~~~~~~~~~~~~[/color]cell.Offset(0, 6).Value = cell.Value
If you have time to run the following code along with taking some of the steps manually indicated at the Rem ) points then I think it will be clear exactly what I want and what bits are missing in the codes. (...... I had to do a few things manually still as
_ 1) I seemed to need to re select the selection as at some point in the code running it became unselected in the first code
_ 2) I still need to add the BB Code Tags Manually as I could not find a way to do that.
........)

So Basically for the code I have presently I
_1) Select the text I want to get in a modified form in the Clipboard. Run The first code
Rem 1)

Sub CopySelection()
This make a copy of, and pastes back, the selected text.
So I now have two identical Text sections
I then must reselect either of the two identical Text sections

_2) I run the second code,.....
Rem 2)
Sub ReplaceSpacesGreaterThanOne()
____.......and the selection changes to

Code: Select all

110~~~~~~If cell.Offset(0, 6).Value < cell.Value Then
120~~~~~~~~~~~~~cell.Offset(0, 6).Value = cell.Value
Note: I am asked if I want to look further to do the change in the rest of the Word Document. I answer No, ( because I do not want to! ). I would like this to be done by the code also if possible . - I did tried a…..
Application.DisplayAlerts = False
Application.DisplayAlerts = True
____... pair, - It did not then ask me…. but went on to do it! ( What I do not want! )

At this point I have to add in the BB Code Tags, as I have not been able to think of a way to do that with the code yet, so I do the change manually to get this…………..

Code: Select all

110[color=#BFFFFF]~~~~~~[/color]If cell.Offset(0, 6).Value < cell.Value Then
120[color=#BFFFFF]~~~~~~~~~~~~~[/color]cell.Offset(0, 6).Value = cell.Value
______.......... I then select that and run the third ( last ) code
_ Rem 3)
Sub CopyModifiedTextToClipboardThenDeleteIt()
This last code bit puts the selection in the clipboard, then it deletes it from the Word Document. ( Note I am doing that in two ways. It is unclear to me which is the one I should use, they both seem to do the same, I think? )

_...................................

Thanks

Alan


P.s. If anyone with a solution has time to put some explaining ‘comments on any code , then I would be additionally grateful as I am keen to learn and do as much as I can before I ask for help again.
_.:::……………

Here my code attempt with instructions for bits I do manually in between at the Rem) Points

Code: Select all

' Main Start.
'Run following Codes, taking the Indicated actions in the instructions at Rem between the codes.

Rem 1) ' Select the Text in which you want to replace spaces of greater than one space with Tidlie Things
Sub CopySelection()
Selection.Copy
Selection.PasteAndFormat (wdPasteDefault)
Selection.PasteAndFormat (wdPasteDefault)
End Sub
Rem 2) 'Now re Select either of the two identical Text sections
Rem ==  When asked select No to searching further to end of text   ===!
Sub ReplaceSpacesGreaterThanOne()
'With the selection replace all two spaces with two Tidlie things
    With Selection.Find
    .ClearFormatting: .Replacement.ClearFormatting
    .Text = "  ": .Replacement.Text = "~~" ' I guess this tells it what to do?         Arguments from the macro recording that I do not apper to need:   .Forward = True: .Wrap = wdFindAsk: .Format = False: .MatchCase = False: .MatchWholeWord = False: .MatchKashida = False: .MatchDiacritics = False: .MatchAlefHamza = False: .MatchControl = False: .MatchWildcards = False: .MatchSoundsLike = False: .MatchAllWordForms = False '
    .Execute Replace:=wdReplaceAll ' I guess this Do it
Rem select No to searching further to end of text
'With the selection replace any remaining Tidlie thing followed with a space with two Tidlie things
    .ClearFormatting: .Replacement.ClearFormatting
    .Text = "~ ": .Replacement.Text = "~~"
    .Execute Replace:=wdReplaceAll 'Do it
Rem select No to searching further to end of text

'With the selection Put a BBCode at the Start and Stop of the Tidlie  ~~~~~~    bits
'==                                                                  ==!
'==  So I want here a code lines to replace all strings like this    ==!
'==        ~~~~~~                                                    ==!
'==  with                                                            ==!
'==      |color=#BFFFFF|~~~~~~|/color|                               ==!
'==      ( the | should be square brackets )                         ==!
    End With
End Sub
Rem 3) ( For now Add in the BB Codes as necerssary.   If necerssary, re select the now modified text  )
Sub CopyModifiedTextToClipboardThenDeleteIt()
Rem a)(i) Clipboard Way  'SOURCE: www.TheSpreadsheetGuru.com
'Dim obj As Object   'Early Binding
'Set obj = New dataobject '  Must enable Forms Library: In VB Editor do this:  Tools -- References - scroll down to Microsoft Forms 2.0 Object Library -- put checkmark in.  Note if you cannot find it try   OR IF NOT THERE..you can add that manually: VBA Editor -- Tools -- References -- Browse -- and find FM20.DLL file under C:\WINDOWS\system32 and select it --> Open --> OK.
' or instead of those two lines  Dim obj As New DataObject .    or  next two lines Late Binding equivalent'
Dim obj As Object
Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Dim txt As String: Let txt = Selection.Text: Debug.Print txt 'Copies the selection as a continuous string: Hit Ctrl G to see it in the Immediate window! You will see it with carriage returns , the Copmuter just sees it as a long "Horizontal" string
obj.SetText txt 'Make object's text equal above string variable
obj.PutInClipboard 'Place DataObject's text into the Clipboard
Rem a)(ii) Simple Copy to get in Clipboard
Selection.Copy
Rem b) ' Delete Midified text. - I do not need it there - it is in the Clipboard now, hopefully.
Selection.Delete
End Sub
'
'_____________________________________________________________-


Rem 'Ref
http://www.thespreadsheetguru.com/blog/ ... -clipboard" onclick="window.open(this.href);return false;
http://www.jkp-ads.com/articles/apideclarations.asp" onclick="window.open(this.href);return false;
http://www.excelforum.com/tips-and-tuto ... ost4110688" onclick="window.open(this.href);return false;
http://www.mrexcel.com/forum/general-ex ... issue.html" onclick="window.open(this.href);return false;
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by HansV »

Try this:

Code: Select all

Sub Replace2OrMoreSpaces()
    With Selection.Find
        ' Don't use formatting
        .ClearFormatting
        .Replacement.ClearFormatting
        ' Tell Word not to continue past the end of the selection
        .Wrap = wdFindStop

        ' Don't use wildcards
        .MatchWildcards = False
        ' Search for two spaces
        .Text = "  "
        ' Replace with two tildas
        .Replacement.Text = "~~"
        ' Replace all within selection
        .Execute Replace:=wdReplaceAll

        ' Search for tilda followed by space
        .Text = "~ "
        ' Replace all within selection
        .Execute Replace:=wdReplaceAll

        ' Search for consecutive tildas
        .Text = "~{1,}"
        ' Enclose in BB codes
        .Replacement.Text = "[color=#BFFFFF]^&[/color]"
        ' Use wildcards
        .MatchWildcards = True
        ' Replace all within selection
        .Execute Replace:=wdReplaceAll
    End With
End Sub
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

Hi Hans,
Thanks very much for that quick reply, - after a lot of frustration on this one I did a bit of exercise against the frustration and when I got back I had a solution!!
The solution looks great and thanks to the ‘Comments I think I can understand it
Also having got this solution I have googled a bit further and it certainly seems as if it should work.

But it errors at

.Execute Replace:=wdReplaceAll

With
Runtime error ‘5560’:
“the text in the field "Search for" contains an invalid pattern comparison”


So I tried other simple search fields , like “~~” or "*~~~~" etc. and it does the appropriate change for that search pattern

But when I try any valid Reg Ex Type search patterns from valid ones that I googled, then I get the same error.

I ggogled a lot further..
I added the reference to

"Microsoft VBScript Regular Expressions 5.5"

But I still get the error.

The code does not seem to except any search patterns other than very simple ones

But I learned enough from you solution to come up with a way to do what I want to do as long as I have an Odd number of spaces.

I will keep trying to find out why I cannot get your solution to work

Thanks again
Alan

_........

This will work for me as long as I have an odd number of spaces:

Code: Select all

Sub ReplaceOddNumberOfSpaces()
    With Selection.Find
        ' Don't use formatting
        .ClearFormatting
        .Replacement.ClearFormatting
        ' Tell Word not to continue past the end of the selection
        .Wrap = wdFindStop

        ' Don't use wildcards
        .MatchWildcards = False
        ' Search for two spaces
        .Text = "  "
        ' Replace with two tildas
        .Replacement.Text = "~~"
        ' Replace all within selection
        .Execute Replace:=wdReplaceAll

        ' Search for consecutive tildas and space after
        .Text = "~*~[!~]"
        ' Enclose in BB codes
        .Replacement.Text = "[color=#BFFFFF]^&[/color]"
        ' Use wildcards
        .MatchWildcards = True
        ' Replace all within selection
        .Execute Replace:=wdReplaceAll
    End With
End Sub
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by HansV »

Do you get the error message with the code that I posted, or with your modified version?
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

I get the error with your code

Mine will work as long as I have an odd number of spaces
I am guessing that yours does not recognize the Reg Ex search patterns.

My modified version is basically your code, except that the search pattern I use I googled, for example from here....
https://msdn.microsoft.com/en-us/library/ee440632(v=office.12" onclick="window.open(this.href);return false;).aspx
'https://msdn.microsoft.com/en-us/librar ... e.12).aspx

____.... I am guessing my search pattern is a "normal VBA" type pattern and yours is some advanced Reg Ex thing, maybe?
Last edited by Doc.AElstein on 28 Feb 2016, 20:11, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by HansV »

Strange - the code works for me. Could you attach a sample document on which you get the error message?
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

Hi Hans,

OK I will try to give you a File, -
Although I have a Lot of Excel .xlsm Files, I only ever had WORD .docx Files.

I have never been sure where my WORD Macros that I write are. They seem to be in some normal.dotm place and I have no idea what that means!!

So the File I enclose is my first ever .docm file I ever made!!!
I have put all the codes in a macro module just as I am used to doing in Excel

I Copied all our codes into that macro module and selected my text and ran your code again. I still get the same error.

Alan

P.s. I put a sample in the document of what I have

Code: Select all

110      If cell.Offset(0, 6).Value < cell.Value Then
120             cell.Offset(0, 6).Value = cell.Value

and what i want copied to the clipboard

Code: Select all

110[color=#BFFFFF]~~~~~~[/color]If cell.Offset(0, 6).Value < cell.Value Then
120[color=#BFFFFF]~~~~~~~~~~~~~[/color]cell.Offset(0, 6).Value = cell.Value


And your code is at the start of the only Module in it, ( Modul1 )
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 28 Feb 2016, 20:54, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by HansV »

Hi Alan,

The document that you attached already has multiple spaces converted to tildas ~, so the only thing my macro does is place the BBcode tags around the tildas. It does that without causing an error.
But the macro that I posted was primarily intended to replace spaces with tildas...
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

Hi Hans
HansV wrote:Hi Alan,
The document that you attached already has multiple spaces converted to tildas .......
Yes Sorry about that. I noticed and have deleted that File attatchment and have uploaded the correct one
I have re uploaded the File showing as in the Post.

I have retried your code both in
Word 2007
and
Word 2010
on different computers

I still get the same error. The code does do the tildas, but then errors at the attempt to put the BB Code tags in

Sorry for the trouble, Possibly my versions of Word do not support those sort of search patterns - I still think by me the problem llies in the

"~{1,}"

I guess if it works for you then the problem lies with my older versions. Everything I google tells me that your code should work.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by HansV »

Ah - you probably use comma as decimal separator. Since your signature mentions that you're English, I had assumed that you use point (dot, period) as decimal separator.

Try changing "~{1,}" (with a comma) to "~{1;}" (with a semi-colon).
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

Hi Hans,
Perfekt ( Perfect ) :clapping:
With semi colon it works !!!

How very stupid of me not to notice that.

I usually always check that in Excel Spreadsheets , but not in VBA, I have got used to expecting problems with that in Worksheets.

But in VBA the code convention has always been as in English. So I never expected that in a VBA Code.
I have never had to replace a comer with a semi-colon in any VBA Code.

How very strange that that is the case with Reg Ex search patterns.
I have just googled over 20 large internet sites in English and German covering Reg Ex Stuff. All codes show comers, and there is no mention of this semi-colon problem there!!
One again a Forum reveals the secrets nowhere else to be found!!

Thanks very much for all the trouble
:thankyou:
Alan

P.s. I have my settings set to point for Decimal Separator. But I never the less always am having problems with ; an , - so this is just another one to add to the list
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by HansV »

You are correct that VBA always uses English settings (US English, to be precise). But "~{1;}" is a literal text string that is entered into the Replace dialog in Word, and that dialog uses your local system settings. Therefore we must use a semi-colon instead of a comma.

(In my opinion, it would have been better if Word VBA would automatically use the system separator, but that could lead to problems too...)
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

HansV wrote:You are correct that VBA always uses English settings (US English, to be precise). But "~{1;}" is a literal text string that is entered into the Replace dialog in Word, and that dialog uses your local system settings. Therefore we must use a semi-colon instead of a comma.

(In my opinion, it would have been better if Word VBA would automatically use the system separator, but that could lead to problems too...)
Hi Hans,
Thanks again
That is all very helpful. I was still thinking in this case the separating is in a couple of options,
Here for example, ~{1,30} ( or ~{1;30} ) means I think at least 1 and not more than 30.
So it was difficult to get the point that we are talking about “literal strings”, which I can sort of see that we are, maybe, thanks to your explanation of what VBA is actually doing.
Alan
P.s.

I wish that all Office was in the English ( US ) sepreator conventions and the like. I never stop having problems with this. I just did not expect a problem here. I see now why i should have

P.P.s For the next time:
are these sort of Questions better in the VB/VBA/.Net Sub Forum or in the relavant Excel or Word Sub Forum . I was not too sure whaer to post it originally
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by HansV »

The code is specific to Word, so the Word forum is fine for it, but the VB/VBA/.Net forum would have been OK too.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

OK,thanks!
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by HansV »

Although wildcard search uses a "syntax" that resembles regular expressions, it is more limited, and moreover it is not 100% compatible with regular expressions.

Here are a few links about searching and replacing with wildcards:
Microsoft: Replace text using wildcards
Word MVP site: Finding and replacing characters using wildcards
Graham Mayor: Find and Replace using wildcards
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

Hi Hans,
Thanks again

I see your point; I was maybe confusing two different similar things

The Wildcard and Reg Ex Pattern expressions and conventions are a Nightmare to get to grips with, IMHO.
THanks for the links, I will review them. I think had hit at least one while I was searching. What is lacking I feel is one single concise list. There always seem to be bits missing

In your code for example in this bit

Code: Select all

.Replacement.Text = "[color=#BFFFFF]^&[/color]"
The ^ was telling me where I start, so somehow the [color=#BFFFFF] was then “squeezed in” before the start, and the & was as often just “gluing something on”.
But in the literature there is no clear explanation I could find for that… That was a major stumping thing for me – how to “add on stuff” to the start and end. There was nothing to be found on that. But as you point out I was probably not quite knowing what to search for. Once again with VBA, I find you often need to know the answer to know where to look, again making these Forum’s essential to tap on to the Knowledge and long experience
__.. I guess as always it come down a lot to experience

Alan

And BTW, I see originally I did not make it clear that the code was erroring at the "~{1,}" – instead I was referring to that by “Reg Ex” stuff, so I was in my ignorance misleading a bit there. Sorry about that. Thanks for sticking in and getting me there.!!
Last edited by Doc.AElstein on 25 Nov 2020, 07:00, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by HansV »

The third link in my previous reply has documentation on ^&:
S0890.png
It's worthwhile spending some time to read through all the options.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

Hi Hans,
Thanks again

I will definitely go through all that you have suggested. You will have the experience to know what should be looked at.
But taking that example, in the Image:
^& -- Contents of 'Find What' box (Replace box only)
I do not see that telling me this
_____Something Here^&Something Else here
Will result in you getting as your replaced String
_____Something HereWhat was Found by the searchSomething Else here
Or / and, in plain English the ^& has the effect of giving you as the returned string what you type before it stuck on intimately to what the search found stuck intimately to what you type after it.
After knowing what the ^& is doing, I can now follow that the “^& adds on the contents in the ‘Find what box’ - but again I needed to know the answer first before I understood that explanation..
But I am a lot wiser now in my ….”…….. using VBA to find bits of strings and add and / or replace bits to that string….”
:smile:



Edit Okt 2020
ref
https://eileenslounge.com/viewtopic.php ... 73#p276673
http://www.eileenslounge.com/viewtopic. ... 12#p175712
Last edited by Doc.AElstein on 29 Oct 2020, 11:13, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

Hi
_ So, thanks to the help here I have this one mostly in the bag now! :smile:
_ I have done some codes for myself that I share in case anyone has a similar requirement.
So:
_ A couple of things were still outstanding...
_ (i) I seemed to need to re select the selection as at some point in the code running it became unselected in the first code of mine from the first Post. Maybe this is just something strange that I appear to be seeing it unselected. ...
_ (ii) I was still not sure of the difference in doing a .Copy or a .PutInClipboard. The mysteries on how Clipboards work stumps many.
http://www.mrexcel.com/forum/excel-ques ... ost4043472" onclick="window.open(this.href);return false;

_ So a solution to get over ( or ignore those ) would be the following:
_ I will use the Clipboard ( as it seems a good thing to get a bit of experience with anyway ). But I will rewrite the code slightly as Follows

"Dialogue Find Replace" way Code.
Rem 1) _ The code will take initially the selected test to the Clipboard using the .PutInClipboard Method applied to a Data Object. This dataObject i will exclusively reserve for doing this...........

Rem 2) _______....... here I still have a somewhat roundabout “Bodge” in order still to use the .Find and .Replace which is working on a Selection or Range Object.
I will make a Temporary Word Document , paste to that and select all from that. ( After this, ( possibly optionally at the end ) I will delete this File ). At least there is no discrepancy or uncertainty.....................

Rem 3) This is basically Han’s Sub Replace2OrMoreSpaces()
http://www.eileenslounge.com/viewtopic. ... 03#p175712" onclick="window.open(this.href);return false;
_
(..._ Rem 4) There may be no direct parallel here, but occasionally when using VBA on Excel “Things”, changed settings are still remembered after the code ends an can occasionally cause problems.......
http://www.mrexcel.com/forum/excel-ques ... ost4172171" onclick="window.open(this.href);return false;
_______________.... so for completeness here all settings in the “Find Replace Text Dialogue” “Thing” are reset to the default.....)



_ Rem 5) A second dataobject is used to get the Text from the Clipboard. I could have used the first , but doing it this way helps to make clear that I have to “get everything” from the Clipboard into a data object, from which I then apply the .GetText() method

_ Rem 6) Deleting the temporary Document


Code:Sub AlanHansClipboardTextGetFindReplace() 'Using the "Dialogue Find Replace" way.

Code: Select all

Sub AlanHansClipboardTextGetFindReplace() 'Using the "Dialogue Find Replace" way.
Rem 1) Put Selected Text in Clipboard.
Dim objCliS As dataobject   '**Early Binding.   Object from the class MS Forms, This is for an Object from the class MS Forms. This will be a Data Object of what we "send" to the Clipboard. It has the Methods I need to send text to the Clipboard. I will use this to put Things in the Clipboard. Bringing things out I will do with another Data Object
Set objCliS = New dataobject '**Must enable Forms Library: In VB Editor do this:  Tools -- References - scroll down to Microsoft Forms 2.0 Object Library -- put checkmark in.  Note if you cannot find it try   OR IF NOT THERE..you can add that manually: VBA Editor -- Tools -- References -- Browse -- and find FM20.DLL file under C:\WINDOWS\system32 and select it --> Open --> OK.
' ( or instead of those two lines Dim obj As New DataObject which is the same ).  or  next two lines are...
'Dim objCliS As Object ' ...Late Binding equivalent'
'Set objCliS = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")' http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
Dim Txtin As String: Let Txtin = Selection.Text: Debug.Print Txtin 'Copies the selection as a continuous string: Hit Ctrl G to see it in the Immediate window! You will see it with carriage returns , the Copmuter just sees it as a long "Horizontal" string
objCliS.SetText Txtin 'Make object's text equal above string variable
objCliS.PutInClipboard 'Place current object dataObject into the Clipboard ( Our original selected text ....!!!.... is in that )
'Rem 2) 'Bit of a bodge to get the text in a selection: create a Word file and paste to it
Dim FullFilePathAndFullName As String 'Initial Pigion Hole given for this String variable, and given a special vbNullString "Value", theoretically to simplify comparisons.
Documents.Add: ActiveDocument.Content.Paste 'Make a File Copy in current Application based on Default Type : And Paste from Clipoard ( ...!!!...our original selected text ) using the Default Copy which should at least have all the text, which is all we are interested in here.
ActiveDocument.SaveAs FileName:="TempBBCodeCopyTidledInSpaces.docx", FileFormat:=wdFormatXMLDocument 'Without this the document will not really "exist jet". It has a tempory name ( Used in Windows referrence ), but no path.
Let FullFilePathAndFullName = ActiveDocument.Path & "\" & ActiveDocument.Name
Selection.WholeStory 'Selects whole document which here is just our selection of interest from the oroiginal document
'Rem 3) Han's Text Find Replacement Dialogue 'http://www.eileenslounge.com/viewtopic.php?f=26&t=22603#p175712
    With Selection.Find 'This is the VBA code ( or very similar ) used by Excel when Using the Find eplace text Dialogue box. So this is an improved version of what a macro recording would give.
    .ClearFormatting: .Replacement.ClearFormatting    ' Don't use formatting, ? not sure this comes into the equation ??
    .Wrap = wdFindStop    ' Tell Word not to continue past the end of the selection ( And therefore prevents also a display Alert asking )
    .MatchWildcards = False    ' Don't use wildcards. The default anyway, but in this code is an important concept...
    .Text = "  "    ' Search text is two spaces
    .Replacement.Text = "~~"    ' Replace text is with two tildas.
    .Execute Replace:=wdReplaceAll    ' Replace all within selection. This is the "OK" button!
    .Text = "~ " ' Search text is tilda followed by space
    .Execute Replace:=wdReplaceAll    ' Replace all within selection. This is the "OK" button!
    .Text = "~{1;}" 'or  [~]{1;}  It is still not totally clear whether this is a Reg Ex Pattern or a Wild Card String. Important is that it is a String in a Dialogue to be applied to A ( Word in this case ) document. Sort of as you write in a cell, so the ; , convention must be carefully checked and appropriately used here
    .Replacement.Text = "[color=#BFFFFF]^&[/color]"    ' Enclose in BB codes  ...... This "Wildcard" applies only to the Replace. It inserts the found string, or strings.
    .MatchWildcards = True 'The next line does the Replce, here we are still selecting an option,( Use wildcards )
    .Execute Replace:=wdReplaceAll ' Replace all within selection. This is the "OK" button!
    End With
ActiveDocument.Select 'Re select the...( actually this line alone seems to do it )
Selection.WholeStory '...while document
Rem 4) "Reset the "Find Replace Text Dialogue" "Thing" "
    With Selection.Find
    .ClearFormatting: .Replacement.ClearFormatting: .Text = "": .Replacement.Text = "":  .Forward = True: .Wrap = wdFindAsk: .Format = False: .MatchCase = False: .MatchWholeWord = False: .MatchKashida = False: .MatchDiacritics = False: .MatchAlefHamza = False: .MatchControl = False: .MatchWildcards = False: .MatchSoundsLike = False: .MatchAllWordForms = False '
    End With
Rem 5) Final result to and from Clipboard
'5b) Using again objCliS we put the modified text in the Clipboard, so overwritng the original
objCliS.SetText Selection.Text 'Replace the text in the data object
objCliS.PutInClipboard 'Place current object dataObject into the Clipboard, so putting the modified text in there
'5b) Another data Object to get the data from the clipboard.
Dim objDat As dataobject
Set objDat = New dataobject 'Set to a new Instance ( Blue Print ) of dataobject
'Dim obj As Object
'Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
objDat.GetFromClipboard 'All that is in the Clipboard goes in this Data Object second instance of the Class.
Dim TxtOut As String: Let TxtOut = objDat.GetText() 'retrieve the text in this second instance of the Class. ( In this case all I have in it is the text )
MsgBox prompt:="You dumped in Clipboard this " & vbCr & objCliS.GetText() & vbCr & "and if you try to get it, you should get" & vbCr & TxtOut & ""
Rem 6) Optional to delete Temporary File
ActiveDocument.Close (wdDoNotSaveChanges) 'Giving the option will also prevent being asked for it. You must close. VBA will not let you kill an open sheet, as you are affectively working on a copy, and VBA is assumng the Original can be got at by saving for example.  http://www.mrexcel.com/forum/excel-questions/920451-excel-macro-files.html#post4425428
Kill FullFilePathAndFullName 'Use the Kill wisely!!!!  - where this goes there 'aint no coming back!!
End Sub
_........................................................................................

_....................................

_ I am still struggling a bit with making a distinction sometimes between The Dialogue Find Replace box and the Reg Expressions stuff.

_ So Just for fun I did a comparison code specifically the Reg Ex Stuff.
The thing which always stumps me is getting the pattern right, and as I have an idea what this should be now, I thought while I am here that it would be a good alternative to have….

Reg Exp Code
Rem 1) The string Text will again come from the Selection but will remain throughout as a string. So we will be doing stuff to this string pseudo “ByRef” - modifying and referenced back, so it suits to use the original “Pointed to” Pigeon Hole, that is to say the Variable ORefiginalText, declared as String, will be used throughout at all modifications, ( but noting the VBA made RHS of = Copy made at one point xxx)

Rem 2 ) Initial “space to tidlie Things”
_I have heard a lot of Pros say that Regular Expressions are violence over computer and If you can solve your problem without normal things (just by string functions), you should. I expect my requirement could be done with a complicated code, I have done similar before. But i wanted to practice the Wildcards with Regular Expressions.
But just to keep a bit sane, I do the Tidlies with a Replace, as it is so easy that way, ( and i was not sure of the pattern to do that with Regular Expressions anyway – maybe for another day ? )

Rem 3 ) Setting up the Reg Ex Stuff.
We are talking about an Object that can be used in a similar way to get and ( replace if needed ) in strings in a similar way to that of the “Dialogue Find Replace way “. A very simplified difference would be to say that it works on Text.

Rem 4 ) Test Reg Ex. - A “pattern” is given to the Object. A command “Test(GivenTextString)” of that pattern on a Given Text String will , if one or more matches are found result in the Reg Ex Object effectively being “filled”…

A very important difference, ( which has helped clear up a few things…)…. the required “semi-colon” version of this search pattern to get all my Tidlie things from the first code……
______ "~{1;}"
___________________.......was found in this code to need to be……..
________________________________ "~{1,}"
So this is telling us I guess, as expected, that we are looking at different things here, - a search pattern within the VB RegEx Object

Another difference , I think, and very relevant to my requirement is , as said by Hans, The Reg Ex Pattern syntax is not 100% compatible with that from the Dialogue Find replkace Stuff.
Inparticular here, Up until now my experiments and goggling have suggested it is limiting the ( important to me bit ) to include the original matcheS , that is to say the……….
____ ^&
______________.......which was shown to bring in the actual matched String into the Replacement String in the “ Dialogue Find Replace box way”…..
http://www.eileenslounge.com/viewtopic. ... 03#p175751" onclick="window.open(this.href);return false;
In the replace box:
Doc.AElstein wrote:......
_____Something Here^&Something Else here
Will result in you getting as your replaced String
_____Something HereWhat was Found by the searchSomething Else here
......
( If i am incorrect here , then I have done an awful lot of work unnecessary in a work around, but never mind it was a good learning experience to understand the Reg Ex object. I expect i am incorrect as all the information is in the object to do it!! )

Rem 5) Up until now most of the details I have learned by goggling, or at least checked by googling after experimenting. The following I got by experimenting ( or made up as I went along ).

It seems all the info I need to do just as I did with “Dialogue Find Replace way “ is there ( a bit hidden as the intellisense and Watch Window is not quite as revealing as it maybe should be….)
_..In the Object , or rather the……
¬¬¬¬¬¬¬_______regEx.Execute(ORefiginalText)__”Method”
______________...... which is shown as an Object. ?? in the Watch window....
_____________________.... are groups of things listed under items. I have for example for each found match its position and Length

So I make an array and loop through to put RegiEx’Executes and its index bits in an Array,
RegAndAlldIdxBits()

Rem 6 ) Building the Final text, being careful where to insert things so as not to lead to perversions
Regies and all its Bits Array is looped through to give us the Positions where our BB Code tags are to be inserted. The Original String then is changed appropriately to do this by referring modifications of that strings back to the string itself. ( ByRef , as it were )
Importantly we loop backwards, As the RegEx Given positional info we are using is counted from the left. So as we go “backwards” or “down the list” or “from the right to the left” , we effectively “add” bits “behind us”, so that the next positional info is still valid for use, that is to say we have not by insertions perverted inportant positions.
xxxThe Method used to build up a string is utilizing whereby VBA takes a Copy of the String to the which is to the left of a = when that string is called on the right of the = in question.

_...........................................................

Seems to work…!!.. ( some Testing here ):

http://www.eileenslounge.com/viewtopic. ... 79#p175879" onclick="window.open(this.href);return false;


The code: ( it is a bit messy as the Code window is wrapping the code lines and comments which in the VB Editor are “hidden to the right” . So it is a bit easier to view there. )
Sub REGinaldsExpressingWTFToReplace() 'Using REGular Expresssions as Direct alternative to a "Dialogue Find Replace" way.

Code: Select all

'
Sub REGinaldsExpressingWTFToReplace() 'Using  REGular Expresssions as Direct alternative to a "Dialogue Find Replace" way.
On Error GoTo TheEnd 'Good to do for unpredcable but Likely errors as i don't know what i am doing, so rather than error go and do important stuff before ending
Rem 1) String for Reg Exp from Selection
Dim ORefiginalText As String: Let ORefiginalText = Selection.Text
Rem 2) Replace Tidlies Using simple VBA Strings collection Functions.
Let ORefiginalText = Replace(ORefiginalText, Space(2), "~~", 1, 31) 'Within ORefiginalText, replace 2 spaces,  with  two tidlie wonks, considering ( and outputing ) from positon 1 - so whole string      ,     put a "safty limit" of 31
Let ORefiginalText = Replace(ORefiginalText, "~ ", "~~", 1, -1) '-1 is the special Long number reserved for replace all
Rem 3) Set up our Reg Ex stuff.
Dim regEx As RegExp 'Early Binding so you will need..
Set regEx = New RegExp '....a reference to the "Microsoft vbscript regular expressions 5.5"
'Dim regEx As Object: Set regEx = CreateObject("vbscript.regexp") 'These two lines late Binding alternative
With regEx
    .Global = True 'ignoring cases while regex engine performs the search??
    .MultiLine = True
    .IgnoreCase = False 'restricting regex to find only first match.
End With
Rem 4) Do the Pattern matching ( "Fill up" the Reg Ex object withh all relavant info for a succesful Pattern match
    regEx.Pattern = "~{1,}" ' or  [~]{1,}   Set up the Pattern ( String type ) to be looked for. For Argument types see http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops   or   ask jindon but he will probably not tell me!
        If regEx.Test(ORefiginalText) = True Then '...try regEx.test() Method which attempts to find and hold all matches....If it works ( at least one "Pattern" match found ) then....
        Dim MtchIdx As Long 'Loop Bound variable Count, to go through all matches
        Dim RegAndAlldIdxBits() As String: ReDim RegAndAlldIdxBits(1 To 3, 1 To 1) 'Will hold idx Bits of the found string , the start, and regies long length in a convenient string format as Strings are well behaved usually when they look like numbers  and are taken into typical VBA things expecting Numbers. It must be dynamiic to allow a ReDim as / if more matches are found.
                'Dim vTemp: Let vTemp = regEx.Replace(ORefiginalText, "[color=#BFFFFF]^&[/color]"): Debug.Print vTemp ' Dont work !!
        Rem 5 ) loop through the sucessfull Pattern match items held within the Reg Ex object ( A "list2 is mad deatiling each sucessful match
        '                       Dim Cnt As Long: Let Cnt = regEx.Execute.Count '   Don't work !!!!
            For MtchIdx = 1 To 1000 Step 1 'to consider all the Matches regEx.test() Method Found, which regEx gives an identifieng Number starting at 0. 1000 is chosen arbritrarily, mostly we will stop a long time before this###
            Dim Concat As String 'String to collect ( concatenate ) all our matches into one single string for Function to return
            On Error GoTo EndOfRegiesBit 'Predictably, hopefully the over next line will error when we overshoot the availyble Items held by regEx Object
            ReDim Preserve RegAndAlldIdxBits(1 To 3, 1 To MtchIdx) 'We may only REDim Preserve the last dimension. The first time around it ReDims to what it is. Otherwise it increases a column to take in Regies Values and All his Bits
            Let RegAndAlldIdxBits(1, MtchIdx) = regEx.Execute(ORefiginalText).Item(MtchIdx - 1).Value 'If no error then we have another Item, returned by the .Item() Property. I seem to have learned fron a Jindon's code that    -1 as the index starts at 0 as often for these internally created things
            On Error GoTo 0 'It is genarally good practice to "unplug" the Error handler after use. We do not need it further here. At thois point it would not have been "switched on" to error handlers. Consequently no "VBA Exceptional Error situation was raised and so the Error Handler Statement On Error GoTo = is sufficint to "unplug it". ( Once an Error situation was raised an additional On Error GoTo -1 wouzld have been necerssary initially to take VBA out of its exceptional state. This is because in this exceptional state it, amougst other things, assumes the Error is being taken care of, so ignors all other Error Handler Statements, including On Error GoTo 0 )
            Let RegAndAlldIdxBits(2, MtchIdx) = regEx.Execute(ORefiginalText).Item(MtchIdx - 1).FirstIndex: Let RegAndAlldIdxBits(3, MtchIdx) = regEx.Execute(ORefiginalText).Item(MtchIdx - 1).Length
            Next MtchIdx 'Go back and look again for pattern match
        Else '.. ( No Pattern found not even 1 )....
        End If
Rem 6)  Error Handler Code Section.   http://excelmatters.com/2015/03/17/on-error-wtf/
EndOfRegiesBit: 'We should come here at the point that no more Matches are found, ( but we had at least one ). The next line is the Error handling code section. After that it is normal code
On Error GoTo -1: On Error GoTo TheEnd 'We bring VBA out of its exceptionally by error raised state, then a putting the for unpredictable Error handler back iin overwrites the last one "switched on"
Rem 7) Loop using Regies  all bits Array to put in BB Code tags in Wanted places. Referring changes back to ORefiginalText
Dim posBBStt As Long, posBBStp As Long 'Variables for point in string where
    For MtchIdx = (UBound(RegAndAlldIdxBits(), 2) - 1) To 1 Step -1 'We intend adding things , so we go backwards...http://www.excelforum.com/showthread.php?t=1103077&p=4184673#post4184673    -1  as the last  was doone before the error
    Let posBBStt = (RegAndAlldIdxBits(2, MtchIdx) + 1) 'Reginald's Array has in second "row" the number of characters before the point we want. The position at which we want start putting a BBCode tag in is one to ther right of this
    Let posBBStp = ((posBBStt + RegAndAlldIdxBits(3, MtchIdx)) + 0) 'Prelifery of Params for shg   http://www.mrexcel.com/forum/lounge-v-2-0/911516-mathematics-not-universal-language-2.html#post4383255
    Debug.Print ORefiginalText '  Folowing are examples based on a TestReg string of 123   789  ( or 123~~~789 ) after Rem 2)
    '6a) Inserting Stop BB Code tag. Here a Copy value of ORefiginalText is taken over to the RHS of the = and worked on using String Object Functions
'    = Replace(ORefiginalText, "~", "~[/color]", (posBBStp - 1), 1): Debug.Print ORefiginalText 'Here this would   (   in  ORefiginalText  ,  replace a tidlie  ,  with a Tidlie and the Stop BB Code tag  ,   starting at the tidlie just before where we wanted the BB Code  ,  ( only doing it once ( as would be anyway ) )     ) giving us something like this   ~[/color]789
'     = Left(RegAndAlldIdxBits(1, MtchIdx), (Len(RegAndAlldIdxBits(1, MtchIdx)) - 1)): Debug.Print ORefiginalText 'This would be require for the last line to give us the tidlie string minus 1 tidlie
'      = Left(ORefiginalText, (posBBStt - 1)): Debug.Print ORefiginalText 'This would give the string up to the position just before where we want to insert the start BBCode Tag. This would give something like   123
    'Let ORefiginalText = Left(ORefiginalText, (posBBStt - 1)) & Left(RegAndAlldIdxBits(1, MtchIdx), (Len(RegAndAlldIdxBits(1, MtchIdx)) - 1)) & Replace(ORefiginalText, "~", "~[/color]", (posBBStp - 1), 1)
    'Alternative not requiring regies Value ( Value being the found Tidlie thing. )
'    = Right(ORefiginalText, ((Len(ORefiginalText) - posBBStp) + 1)): Debug.Print ORefiginalText 'This would give the string after the Point where we want to insert the BBCode Stop tag. We need for this the length counting from the right which will be 1 more than the total length ( currently ) and the to be inserted point for the Stop BB Code. This would give something like this  789
'     = Left(ORefiginalText, (posBBStp - 1)) 'This would give the string up to the position just before where we want to insert the stop BBCode Tag. This would give something like   123~~~
    Let ORefiginalText = Left(ORefiginalText, (posBBStp - 1)) & "[/color]" & Right(ORefiginalText, ((Len(ORefiginalText) - posBBStp) + 1)): Debug.Print ORefiginalText
    '6b) Inserting Start BB Code tag. Here a Copy value of last by referring to changed ORefiginalText is taken over to the RHS of the = and manipulated using String Object Functions
    '= Left(ORefiginalText, (posBBStt - 1)): Debug.Print ORefiginalText 'returns like   123
    ' = Right(ORefiginalText, ((Len(ORefiginalText) - posBBStt) + 1)): Debug.Print ORefiginalText ' gives like this  ~~~[/color]789
    Let ORefiginalText = Left(ORefiginalText, (posBBStt - 1)) & "[color=#BFFFFF]" & Right(ORefiginalText, ((Len(ORefiginalText) - posBBStt) + 1)): Debug.Print ORefiginalText
    'Let ORefiginalText = ORefiginalText & vbCr  ' Strangely this "bodge seemed to be needed initially. It was as if a return was always lost when pasting into a Forum. So putting one in where not wanted after each match cured that.- 2  became 1 so has 1 where wanted!!.  but never the less had a lot at the end !?
    Next MtchIdx ' Got to next held item in Reginald
Rem 8)  Clipboard
'8a) Dump in Clipboard This used to put in Clipboard
Dim objCliS As dataobject   '**Early Binding.   This is for an Object from the class MS Forms. This will be a Data Object of what we "send" to the Clipboard. So I name it CLIpboardSend. But it is a DataObject. It has the Methods I need to send text to the Clipboard
Set objCliS = New dataobject '**Must enable Forms Library: In VB Editor do this:  Tools -- References - scroll down to Microsoft Forms 2.0 Object Library -- put checkmark in.  Note if you cannot find it try   OR IF NOT THERE..you can add that manually: VBA Editor -- Tools -- References -- Browse -- and find FM20.DLL file under C:\WINDOWS\system32 and select it --> Open --> OK.
' ( or instead of those two lines  Dim obj As New DataObject ).    or  next two lines are.....Late Binding equivalent'
'Dim obj As Object'  Late Binding equivalent'   If you declare a variable as Object, you are late binding it.  http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/
'Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")' http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
objCliS.SetText ORefiginalText 'Make Data object's text equal to a copy of ORefiginalText
objCliS.PutInClipboard 'Place current Data object into the Clipboard
'8b) Get from clipboard. This a Another Object from class to be sure we have the data in the Clipboard
Dim objDat As dataobject
Set objDat = New dataobject 'Set to a new Instance ( Blue Print ) of dataobject
'Dim obj As Object
'Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
objDat.GetFromClipboard 'All that is in the Clipboard goes in this Data Object second instance of the Class.
Dim TxtOut As String: Let TxtOut = objDat.GetText() 'retrieve the text in this second instance of the Class. ( In this case all I have in it is the text )
MsgBox prompt:="You dumped in Clipboard this " & vbCr & objCliS.GetText() & vbCr & "and if you try to get it, you should get" & vbCr & TxtOut & ""
'
Rem 9) End clean up.
TheEnd: ' ( Come here always, even on a unpredictable error )
Set regEx = Nothing '   Good practice...   maybe....
Set objCliS = Nothing '  .......   http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html#post4414065
Set objDat = Nothing
End Sub
'
'
Sub StringObjectFunctions()
Dim str As String
Let str = "12345648910"
Let str = Right(str, 9) '"This is "345648910"

Let str = "12345648910"
Let str = Replace(str, "4", "x", 1, 1) 'This is "123x5648910"

Let str = "12345648910"
Let str = Replace(str, "4", "x", 1, 2) 'This is "123x56x8910"

Let str = "12345648910"
Let str = Replace(str, "4", "x", 3, 1) 'This is "3x5648910"
End Sub

Rem Ref Using Wildcards in Find and Replace ( and Reg Ex !! ? ! )
The reference titles and content can be a bit confusing, IMHO, as is the whole Find and Replace Theme. One needs to bear in mind I think that we may be talking of the Finding Wildcards which aid in the search and additionally Replacing Wildcards which allow you to manipulate the replacement string. This was a major point I was missing.
Additionally as Han’s pointed out there is a similar but not 100% compatible Reg Ex “Thing”. In general this Reg Ex “Thing” is defined as “ a sequence of characters that define a search pattern “. In VBA and VB 6 this would be an Object requiring a reference , ( or alternatively a Late Binding call ) to prepare computer memory initially for using these features in a code.
But these articles are initially very similar talking about Wildcards in Text Replacement
https://support.office.com/en-US/articl ... 4173D3D6E4" onclick="window.open(this.href);return false;
and similarly using regular expressions in Text Replacement
https://support.office.com/en-us/articl ... b0ad1c427f" onclick="window.open(this.href);return false;
It seems no preliminary reference is required and the options are available in the Word Searching and replacing. ( And in the second article they do actually address the , comer ; semi-colon issue!!! )

Rem Ref Reg Ex XL Reg Reginald Expressions Object Wonk, stuff
http://www.excelforum.com/excel-new-use ... ion-2.html" onclick="window.open(this.href);return false;
http://www.excelforum.com/excel-program ... ost4311293" onclick="window.open(this.href);return false;
https://msdn.microsoft.com/de-de/library/xwewhkd1(v=vs.110" onclick="window.open(this.href);return false;).aspx
Rem Ref Data Objects, Clipboards
http://www.mrexcel.com/forum/excel-ques ... ost3988279" onclick="window.open(this.href);return false;
http://excelmatters.com/2013/10/04/late ... ataobject/" onclick="window.open(this.href);return false;
http://www.mrexcel.com/forum/excel-ques ... ost3747351" onclick="window.open(this.href);return false;
Rem Ref Copy
http://www.mrexcel.com/forum/excel-ques ... ost3988279" onclick="window.open(this.href);return false;

Thankyou
Alan


_.............................
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also