Hi
_ So, thanks to the help here I have this one mostly in the bag now!
_ 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