Nested Substitution functions Simplified in to a Single Substitution function?

[MEUserII]

I have the following formula enter in to "A2": =SUBSTITUTE( (SUBSTITUTE( ("$GR$1"), (1), ("") )), ("$"), ("") ) ; is there a way to make both nested substitutions of "1" and "$" with a single substitute function?

 

[Dryver14]

Whats in GR1 before and after

 

[MEUserII]

$GR$1 is: =ADDRESS( (ROW() ), (COLUMN() ), (1) ) ; I am using this as a test to see how to combine nested Substitution functions in to one single Substitution function.

Anyone have a solution?

 

[shg]

You need to nest them.

You could write your own function in VBA to do that, though. Not sure it's worth the trouble.

 

[MEUserII]

You need to nest them.

You could write your own function in VBA to do that, though. Not sure it's worth the trouble.

Got it, to make sure I understand, you mean by writing my own function you mean to write a user defined function (UDF)?

 

[Peter_SSs]

.. to write a user defined function (UDF)?

I believe that is what shg meant. I had been playing with this one. It uses Regular Expressions which has special meanings for some characters (eg $ sign) so I haven't tested this thoroughly & it may not be that robust.

It does require that all values being searched for are replaced with the same text (but that can be something other than the null string "" if you want) and it does give the option of searching in a case-sensitive way if you want. See examples of its use below. The values being searched for must be separated by the "|" character and as the function stands, that character cannot be one of the ones searched for.

Function SubstituteMultiple(s As String, sFindVals As String, Optional sReplacement As String = "", Optional bMatchCase As Boolean = True) As String
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "([^a-zA-Z0-9\|])"
  RX.Pattern = RX.Replace(sFindVals, "\$1")
  RX.IgnoreCase = Not bMatchCase
  SubstituteMultiple = RX.Replace(s, sReplacement)
End Function

Excel Workbook
	A	B
1	$GR$1	GR
2	$GR$1	@GR@@
3	Bob	%ob
4	Bob	%o%
5	Cat	t
6	This is a longer than three words	Thvowels vowels vowel lvowelngvowelr thvoweln thrvowelvowel wvowelrds
7	This is another example	This is anoyyer exyy
Sheet1

 

[Fazza]

This looks like a small part of a bigger spreadsheet. If you're interested, you may get a different/better answer if some more information/context is provided. regards

 

[shg]

I was thinking of something much more simpleminded:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]$GR$1[/td][td="bgcolor:#E5E5E5"]GR[/td][td]B2: =MSub(A2, 1, "", "$", "")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

13579​

[/td][td="bgcolor:#E5E5E5"]24680[/td][td]B3: =MSub(A3, 1,2,3,4,5,6,7,8,9,0)[/td][/tr]
[/table]

Function MSub(s As String, ParamArray FindRepl() As Variant) As String
  Dim i As Long
  
  MSub = s
  For i = 0 To UBound(FindRepl) Step 2
    MSub = Replace(MSub, FindRepl(i), FindRepl(i + 1))
  Next i
End Function