Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Substitute function, twice in one formula

  1. #1
    Join Date
    16th September 2005
    Posts
    7

    Substitute function, twice in one formula

    Is it possible to use the substitute function twice within the same formula? I need to substitute two separate text strings in cell A1. I'm able to do one, but when I use the ampersand to put the second substitute function into the formula, it does only the second substitute and not the first.

    Thanks!
    Dan

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,528

    Re: Substitute function, twice in one formula

    You can next the functions. For example:
    =SUBSTITUTE(SUBSTITUTE("abcde","a","z"),"b","2")
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

  3. #3
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    3,368

    Re: Substitute function, twice in one formula

    Dan,

    I'm not sure exactly what you're trying to do, but you can have a Substitute function within another Substitute function.

    Assuming cell A1 contains "test me for changes", the formula

    =SUBSTITUTE(SUBSTITUTE(A1,"me","you"),"for","against")

    will firstly substitute "me" with "you" then "for" with "against" and produce the result 'test you against changes'.

    If you change the value you have previously changed in the first substitute it will appear as though only the second one is being actioned, e.g.

    =SUBSTITUTE(SUBSTITUTE(A1,"me","you"),"you","them")

    will produce ' test them for changes', but this is the result of making the first change, then amending the result of that.

    Regards,
    Batman.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Substitute Function Non-case Sensitive
    By keekooceeaou in forum Excel General
    Replies: 5
    Last Post: January 7th, 2011, 08:45
  2. Substitute For Nested If
    By <3cheesybread in forum Excel General
    Replies: 2
    Last Post: April 19th, 2008, 09:44
  3. Substitute Function With Find, Mid & Left
    By Reggie in forum Excel General
    Replies: 4
    Last Post: May 29th, 2007, 21:02
  4. Substitute for IF statement
    By cparaske in forum Excel General
    Replies: 3
    Last Post: July 30th, 2005, 02:56
  5. Substitute the name of an object
    By bobwhosmiles in forum Excel General
    Replies: 10
    Last Post: October 21st, 2004, 18:24

Bookmarks

Posting Permissions

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