+ Reply to Thread
Results 1 to 5 of 5

Using Substitute

  1. #1
    Registered User
    Join Date
    01-29-2013
    Location
    Quebec
    MS-Off Ver
    MS365 (PC) Version 2104
    Posts
    3

    Using Substitute

    Hi,

    I got a column (C) containing a POSTAL CODE and column (D) CITY TO. If the postal code is "A0G" then I want the D column to be "GASPE". IF the C (postal code) column is NOT "A0G" I want the D column to retain its current value... I did a SUBSTITUTE in my form. Check line 14




    =SUBSTITUTE(C14,"A0G","GASPE")



    But if the C column is NOT "A0G" then the D column SHOULD NOT GET overwriting. UNKNOWN should stay in the D column IF C column is NOT "A0G".

    Should I use SUBSTITUTE or other function to accomplish this?

    Thanks in advance.

    test_substitute.xlsx

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Using Substitute

    Hi. Before we get going on this one...

    Is your forum profile showing the Excel version that you need this request to work with?

    The best solutions often rely on knowing WHICH version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

    The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

    Cheers,

    Glenn.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Using Substitute

    Cells can contain a formula, or a value, but not BOTH. In E3:

    =IF(C3="A0G","GASPE",D3)

    copied down
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-29-2013
    Location
    Quebec
    MS-Off Ver
    MS365 (PC) Version 2104
    Posts
    3

    Re: Using Substitute

    Hi Glenn,

    I've updated my Excel version in my profile.

    Is there no way to ONLY do the SUBSTITUTE IF the D Column (CITY TO) is NULL??

    Thanks.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Using Substitute

    When you say NULL, I imagine that you are asking if the formula can be put into the empty cells in column D.
    If there is a way to do that it would involve VBA of which I know too little to help, however you may want to ask a moderator to move this thread to that forum and see if one of the contributors over there can help.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 09-27-2020, 11:00 PM
  2. [SOLVED] Indirect "Substitute(Substitute(" not working for multiple spaces
    By Ochimus in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-03-2018, 04:01 PM
  3. Replies: 4
    Last Post: 10-28-2014, 11:04 PM
  4. How to use a substitute formula to substitute text entries to a different text entry
    By andybocchi in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-01-2010, 07:50 PM
  5. substitute for =
    By Gary's Student in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 07:05 PM
  6. substitute for =
    By Gary's Student in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  7. substitute
    By AMK in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1