StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Regular Expressions, or Regexp, are what you are looking for I think.</p> <p>The following Pattern</p> <pre><code>([A-Z0-9]*)!(\${0,1})([A-Z]{1,3})(\${0,1})([0-9]*) </code></pre> <p>will match anything like "Sheet1!A1", "Sheet1!$A$1", "Sheet1!$A1", "Sheet1!A$1"</p> <p>Explanation:</p> <pre><code>([A-Z0-9]*)! = Find anything that is before "!" (\${0,1}) = $ or nothing ([A-Z]{1,3}) = between one and three letters ([0-9]*) = Any number </code></pre> <p>You should easily be able to modify that pattern to match only what you want. In particular, ([A-Z0-9]*)!(\${0,1})B(\${0,1})1, will only match something with B($)1 in it... Construct the Regexp pattern with string manipulation and should be good.</p> <p>You'll need to reference (Tool > Reference) the "Microsoft VBScript Regular Expressions 5.5"</p> <p>Try the following code, this should give you all the tools to achieve your goal</p> <pre><code>Sub ReplaceReference() ' Reference: Microsoft VBScript Regular Expressions 5.5 Dim RegEx As Object Set RegEx = New RegExp Dim s As String ' Here I have hardcoded the reference to the original cell for demonstration purposes s = "Sheet1!$AB$2" ' Replacement: New sheetname, New Column, new row number Dim NewCol As String, NewRow As String NewCol = "C" NewRow = "10" Dim NewSheet As String NewSheet = "Sheet2" With RegEx .Pattern = "([A-Z0-9]*)!(\${0,1})([A-Z]{1,3})(\${0,1})([1-9]*)" .IgnoreCase = True .Global = True End With Debug.Print RegEx.Replace(s, NewSheet & "!" & "$2" & NewCol & "$4" & NewRow) End Sub </code></pre> <p>Cheers, Julien</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload