Page 1 of 2 12 LastLast
Results 1 to 10 of 17
Like Tree1Likes

Multiple SUBSTITUTE Functions

This is a discussion on Multiple SUBSTITUTE Functions within the Excel Questions forums, part of the Question Forums category; Is there a way to structure Multiple SUBSTITUTE Functions? I have two values in a text body within a cell ...

  1. #1
    Board Regular AndrewKent's Avatar
    Join Date
    Jul 2006
    Location
    Edinburgh
    Posts
    875

    Default 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. #2
    Board Regular
    Join Date
    Aug 2003
    Location
    England
    Posts
    4,644

    Default Re: Multiple SUBSTITUTE Functions

    Hello,

    do you mean this?

    =SUBSTITUTE(SUBSTITUTE(B26,"Amount",$H$8),"Name",H7)
    PJRY likes this.
    -------------------------
    Hope this is helpful.
    -------------------------
    only a drafter,
    but broadening my Excel knowledge.

  3. #3
    Board Regular AndrewKent's Avatar
    Join Date
    Jul 2006
    Location
    Edinburgh
    Posts
    875

    Default Re: Multiple SUBSTITUTE Functions

    Brilliant, thanks!

  4. #4
    New Member
    Join Date
    Apr 2013
    Posts
    1

    Default Re: Multiple SUBSTITUTE Functions

    Quote Originally Posted by AndrewKent View Post
    Brilliant, thanks!
    I need some help PLEASE?!?!?!?!

    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. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    24,830

    Default 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.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  6. #6
    Biz
    Biz is offline
    Board Regular
    Join Date
    May 2009
    Location
    Perth, Australia
    Posts
    1,326

    Default Re: Multiple SUBSTITUTE Functions

    Quote Originally Posted by Peter_SSs View Post
    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. #7
    New Member
    Join Date
    Dec 2013
    Posts
    4

    Default 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.
    Last edited by rupeadupe; Dec 19th, 2013 at 01:18 PM. Reason: pasting error

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    13,230

    Default Re: Multiple SUBSTITUTE Functions

    Quote Originally Posted by rupeadupe View Post
    @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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  9. #9
    New Member
    Join Date
    Dec 2013
    Posts
    4

    Default 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. #10
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    13,112

    Default 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)


    Looks like license plates ...
    Last edited by shg; Dec 19th, 2013 at 02:26 PM.
    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com