Thanks:  0
Likes:  0

1. ## Multiple SUBSTITUTE Functions

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

2. ## Re: Multiple SUBSTITUTE Functions

Hello,

do you mean this?

=SUBSTITUTE(SUBSTITUTE(B26,"Amount",\$H\$8),"Name",H7)

3. ## Re: Multiple SUBSTITUTE Functions

Brilliant, thanks!

4. ## Re: Multiple SUBSTITUTE Functions

Originally Posted by AndrewKent
Brilliant, thanks!

I did some excel training what seems like over ten years ago, and boy have things changed a little

I am trying to make a recipe sort of sheet for my daughters, but im stuck.

in column B I wish to enter g, ts, tb, c, m, and for it to appear as grams, teaspoons, tablespoons, cups, mls

im sure there is a really easy way to make it happen, but alas, I have not found it lol otherwise its a lot of typing for me!

I guess thats it, will thank anyone in advance for a reply or help - THANKS

5. ## Re: Multiple SUBSTITUTE Functions

Welcome to the MrExcel board!

I'm assuming that you want the longer words to actually replace what you type in column B.
(If you didn't need that and would be happy with the full words appearing in, say, column C then you could use a VLOOKUP formula in column C)

A. You could (I'm not recommending this) add AutoCorrect entries to change "c" to "cups" etc. This is simple enough to do BUT would mean "c" changing to "cups" in any column in any spreadsheet or any other Office program until you remembered to go back in and delete those AutoCorrect entries. Way too risky in my mind!

B. Better option, but does require macros to be enabled and your file saved as a macro-enabled file (.xlsm) if using Excel 2007 or later.
This option assumes that column B only gets the g, c etc. That is if you want 3 cups, you would put the "3" in column A and the "c" in column B
To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window and test.
Code:
```Option Explicit
Option Base 1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Abbrev
Dim changed As Range, c As Range
Dim m As Variant

Const AbbrevCol As String = "B"

Set changed = Intersect(Target, Columns(AbbrevCol))
If Not changed Is Nothing Then
Abbrev = Array("g", "grams", "ts", "teaspoons", "tb", "tablespoons", "c", "cups", "m", "mls")
Application.EnableEvents = False
For Each c In changed
m = Application.Match(c.Value, Abbrev, False)
If IsNumeric(m) Then
If m Mod 2 = 1 Then
c.Value = Abbrev(m + 1)
End If
End If
Next c
Application.EnableEvents = True
End If
End Sub```
If I haven't guessed well enough what you want, post back with more details.

6. ## Re: Multiple SUBSTITUTE Functions

Originally Posted by Peter_SSs
Welcome to the MrExcel board!

I'm assuming that you want the longer words to actually replace what you type in column B.
(If you didn't need that and would be happy with the full words appearing in, say, column C then you could use a VLOOKUP formula in column C)

A. You could (I'm not recommending this) add AutoCorrect entries to change "c" to "cups" etc. This is simple enough to do BUT would mean "c" changing to "cups" in any column in any spreadsheet or any other Office program until you remembered to go back in and delete those AutoCorrect entries. Way too risky in my mind!

B. Better option, but does require macros to be enabled and your file saved as a macro-enabled file (.xlsm) if using Excel 2007 or later.
This option assumes that column B only gets the g, c etc. That is if you want 3 cups, you would put the "3" in column A and the "c" in column B
To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window and test.
Code:
```Option Explicit
Option Base 1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Abbrev
Dim changed As Range, c As Range
Dim m As Variant

Const AbbrevCol As String = "B"

Set changed = Intersect(Target, Columns(AbbrevCol))
If Not changed Is Nothing Then
Abbrev = Array("g", "grams", "ts", "teaspoons", "tb", "tablespoons", "c", "cups", "m", "mls")
Application.EnableEvents = False
For Each c In changed
m = Application.Match(c.Value, Abbrev, False)
If IsNumeric(m) Then
If m Mod 2 = 1 Then
c.Value = Abbrev(m + 1)
End If
End If
Next c
Application.EnableEvents = True
End If
End Sub```
If I haven't guessed well enough what you want, post back with more details.
Very nice approach.

7. ## Re: Multiple SUBSTITUTE Functions

@Peter_SSs,
I have a similar situation requiring your assistance, relative to a makeshift find/replace/decoding scenario. I have a list of characters (alpha, numeric and otherwise) in column A. I have the characters they need to be replaced with in column B. Finally, I have the text in column C. In this formula I require each character within text in column C to be replaced to with the corresponding characters based on the table in columns A and B. For example, the text 15KI is in cell C2. Cell A2 indicates 1, cell B2 indicates X, cell A3 indicates 5, cell B3 indicates U, cell A4 indicates K, cell B4 indicates 6, cell A5 indicates I, cell B5 indicates 8, and so on with all possible characters indicated in column C. So, the output (in column D2, or wherever) would be XU68. A few points: the text in column C can have anywhere from 1 to 15 total characters. The number of rows in columns A and B are 38 (including headers). Please assist. situation requiring your assistance, relative to a makeshift find/replace/decoding scenario. I have a list of characters (alpha, numeric and otherwise) in column A. I have the characters they need to be replaced with in column B. Finally, I have the text in column C. In this formula I require each character within text in column C to be replaced to with the corresponding characters based on the table in columns A and B. For example, the text 15KI is in cell C2. Cell A2 indicates 1, cell B2 indicates X, cell A3 indicates 5, cell B3 indicates U, cell A4 indicates K, cell B4 indicates 6, cell A5 indicates I, cell B5 indicates 8, and so on with all possible characters indicated in column C. So, the output (in column D2, or wherever) would be XU68. A few points: the text in column C can have anywhere from 1 to 15 total characters. The number of rows in columns A and B are 38 (including headers). Please assist.

8. ## Re: Multiple SUBSTITUTE Functions

@Peter_SSs,
I have a similar situation requiring your assistance, relative to a makeshift find/replace/decoding scenario. I have a list of characters (alpha, numeric and otherwise) in column A. I have the characters they need to be replaced with in column B. Finally, I have the text in column C. In this formula I require each character within text in column C to be replaced to with the corresponding characters based on the table in columns A and B. For example, the text 15KI is in cell C2. Cell A2 indicates 1, cell B2 indicates X, cell A3 indicates 5, cell B3 indicates U, cell A4 indicates K, cell B4 indicates 6, cell A5 indicates I, cell B5 indicates 8, and so on with all possible characters indicated in column C. So, the output (in column D2, or wherever) would be XU68. A few points: the text in column C can have anywhere from 1 to 15 total characters. The number of rows in columns A and B are 38 (including headers). Please assist. situation requiring your assistance, relative to a makeshift find/replace/decoding scenario. I have a list of characters (alpha, numeric and otherwise) in column A. I have the characters they need to be replaced with in column B. Finally, I have the text in column C. In this formula I require each character within text in column C to be replaced to with the corresponding characters based on the table in columns A and B. For example, the text 15KI is in cell C2. Cell A2 indicates 1, cell B2 indicates X, cell A3 indicates 5, cell B3 indicates U, cell A4 indicates K, cell B4 indicates 6, cell A5 indicates I, cell B5 indicates 8, and so on with all possible characters indicated in column C. So, the output (in column D2, or wherever) would be XU68. A few points: the text in column C can have anywhere from 1 to 15 total characters. The number of rows in columns A and B are 38 (including headers). Please assist.
I think it would be useful to show us your full list with their substitutions. What I am concerned about is the possibility of changing one thing only to have a later substitution change it to something else. While the odds are slim that you list will do that, it is better to know now than have you query back that something isn't working right.

9. ## Re: Multiple SUBSTITUTE Functions

 Original New 0 J 1 I 2 H 3 G 4 F 5 E 6 D 7 C 8 B 9 A A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 0 K Z L Y M X N W O V P U Q T R S S R T Q U P V O W N X M Y L Z K - .

10. ## Re: Multiple SUBSTITUTE Functions

Code:
```Function rd(ByVal sInp As String) As String
Dim i           As Long
Dim sChr        As String

sInp = UCase(sInp)

For i = 1 To Len(sInp)
sChr = Mid(sInp, i, 1)
Select Case sChr
Case "0" To "9"
rd = rd & Chr(Asc("J") - Asc(sChr) + Asc("0"))
Case "A" To "I"
rd = rd & Chr(Asc(sChr) - Asc("A") + Asc("1"))
Case "J"
rd = rd & "0"
Case "K" To "Z"
rd = rd & Chr(Asc("Z") - Asc(sChr) + Asc("K"))
Case "-"
rd = rd & "."
Case Else
rd = rd & sChr
End Select
Next i
End Function```
 A B 1 Now is the time for all good men 2 WVN 9R Q85 Q9X5 6VS 1YY 7VV4 X5W A2: =rd(A1)

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•