Multiple SUBSTITUTE Functions

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
Is there a way to structure Multiple SUBSTITUTE Functions? I have two values in a text body within a cell that I want to change, this works...

=SUBSTITUTE(B26,"Amount",$H$8)

...however I want to include "Name" (Which is linked to H7) as well.

Andy
 
Here is another Function to consider (same underlying concept as shg's code, but using string-stuffing instead of concatenations)...

Code:
Function LetterNumberSwap(ByVal S As String) As String
  Dim X As Long
  LetterNumberSwap = UCase(S)
  For X = 1 To Len(LetterNumberSwap)
    Select Case Mid(LetterNumberSwap, X, 1)
      Case 0 To 9
        Mid(LetterNumberSwap, X, 1) = Chr(74 - Mid(LetterNumberSwap, X, 1))
      Case "A" To "J"
        Mid(LetterNumberSwap, X, 1) = (Asc(Mid(LetterNumberSwap, X, 1)) - 64) Mod 10
      Case "K" To "Z"
        Mid(LetterNumberSwap, X, 1) = Chr(165 - Asc(Mid(LetterNumberSwap, X, 1)))
    End Select
  Next
End Function
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Excellent. Thank you both for the assistance.
One last question: what about a reverse function-changing back to the original text when inputting the new text?
 
Last edited:
Upvote 0
Excellent. Thank you both for the assistance.
One last question: what about a reverse function-changing back to the original text when inputting the new text?
Welcome to the MrExcel board!

If you might in the future want to jumble up the substitutions and have the function actually use what is in columns A:B as you first stated, then here is another function to consider.
As you can see in column E, by adding the optional second argument of TRUE (or 1), it will also do reverse substitutions.

My function does not convert lower case letters (see row 11) but could force upper case like the other suggested functions if required.

Code:
Function Repl(ByVal s As String, Optional Rev As Boolean = False) As String
  Dim i As Long, n As Long, L As Long, c1 As Long, c2 As Long, UBA As Long
  Dim a
  Dim Found As Boolean
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 2)
  UBA = UBound(a, 1)
  L = Len(s)
  If Rev Then
    c1 = 2
    c2 = 1
  Else
    c1 = 1
    c2 = 2
  End If
  For n = 1 To L
    Found = False
    i = 1
    Do
      If CStr(a(i, c1)) = Mid(s, n, 1) Then
        Found = True
        Mid(s, n, 1) = a(i, c2)
      Else
        i = i + 1
      End If
    Loop Until Found Or i > UBA
  Next n
  Repl = s
End Function

Excel Workbook
ABCDE
1OriginalNewReplaceReverse
20JADAY141LADAY
31IZEA5K51EZEA5
42H5NEEEW555NEE
53GPBM7U2XCPBM7
64F6L-GDY.76L-G
75EW9SDNAR4W9SD
86DAXMM1MXXAXMM
97CF#/P6#/UF#/P
108BWU0JNPJ0WU0J
119AWfk6NfkDWfk6
12A1
13B2
14C3
15D4
16E5
17F6
18G7
19H8
20I9
21J0
22KZ
23LY
24MX
25NW
26OV
27PU
28QT
29RS
30SR
31TQ
32UP
33VO
34WN
35XM
36YL
37ZK
38-.
Sheet1
 
Upvote 0
@Peter
You should change your name to Excel Master or something along those lines. Fantastic advice and solution. Thanks for the help.
<o:p></o:p>
 
Upvote 0
Welcome to the MrExcel board!

If you might in the future want to jumble up the substitutions and have the function actually use what is in columns A:B as you first stated, then here is another function to consider.
As you can see in column E, by adding the optional second argument of TRUE (or 1), it will also do reverse substitutions.

My function does not convert lower case letters (see row 11) but could force upper case like the other suggested functions if required.

Code:
Function Repl(ByVal s As String, Optional Rev As Boolean = False) As String
  Dim i As Long, n As Long, L As Long, c1 As Long, c2 As Long, UBA As Long
  Dim a
  Dim Found As Boolean
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 2)
  UBA = UBound(a, 1)
  L = Len(s)
  If Rev Then
    c1 = 2
    c2 = 1
  Else
    c1 = 1
    c2 = 2
  End If
  For n = 1 To L
    Found = False
    i = 1
    Do
      If CStr(a(i, c1)) = Mid(s, n, 1) Then
        Found = True
        Mid(s, n, 1) = a(i, c2)
      Else
        i = i + 1
      End If
    Loop Until Found Or i > UBA
  Next n
  Repl = s
End Function
I know this will come as a surprise to you :eek:, but I have an alternate UDF which does what your UDF does but is ever so slightly more compact :LOL: ...

Code:
Function Repl(ByVal S As String, Optional Rev As Boolean = False) As String
  Dim X As Long, Pos As Long, Col(1 To 2) As String
  Col(1) = Join(WorksheetFunction.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp))), "")
  Col(2) = Join(WorksheetFunction.Transpose(Range("B2", Cells(Rows.Count, "B").End(xlUp))), "")
  For X = 1 To Len(S)
    Pos = InStr(Col(1 - Rev), Mid(S, X, 1))
    If Pos Then Mid(S, X, 1) = Mid(Col(2 + Rev), Pos, 1)
  Next
  Repl = S
End Function
 
Upvote 0
I have a similar question with multiple substitutions. My web-page allows users to search through pictures by keyword. The keywords are currently bib# from a race. I have another spreadsheet with first name, last name, bib number. I have used CONCATENATE to place all of this information into one column. I would like to add the first name, last name to the keywords. Another hurdle is that the bib# are 1-3000. So I do not want to substitute the 1 in 991 with the first and last name associated with bib#1.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top