Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Substitute Function Non-case Sensitive

  1. #1
    Join Date
    29th June 2006
    Location
    Poland
    Posts
    67

    Substitute Function Non-case Sensitive

    Hi,

    is it possible to make a SUBSTITUTE finction non-case sensitive?
    For example I want to replace all letters "e" and "E" in a cell.

    Thanks for answers
    Last edited by keekooceeaou; November 2nd, 2006 at 19:10.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,284

    Re: Substitute Function Non-case Sensitive

    Can you not just use nested formula?

    VB:
    =SUBSTITUTE(SUBSTITUTE(C5,"e","Q"),"E","Q") 
    
    

    Cheers
    Andy


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,765

  4. #4
    Join Date
    29th June 2006
    Location
    Poland
    Posts
    67

    Re: Substitute Function Non-case Sensitive

    Quote Originally Posted by Dave Hawley
    Or =SUBSTITUTE(UPPER(A1),"E","")
    Thanks, that's exactly what I needed.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th January 2011
    Posts
    4

    Re: Substitute Function Non-case Sensitive

    Hi

    I'm trying to replace some strings and the formula SUBSTITUTE ( UPPER (A1),"JIM","Tom") works just fine except it returns the other characters in the cell as ALL CAPS.

    I.e. "Jim Franks" will become "Tom FRANKS"

    Can anyone help with an option that doesn't affect the other characters?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,229

    Re: Substitute Function Non-case Sensitive

    Hello cristihno,

    Welcome to Ozgrid. We're glad to have you on board, however, please note:

    Posting your question in threads started by others is a violation of the forum rules and is known as thread hijacking. Posting solutions is acceptable.

    ALWAYS start a new thread for your question and if you think it is helpful to clarify your thread you may include a link back this thread (or any other).

    Start a new thread and be sure to following these guidelines when giving a title to your thread:

    Thread titles are used in searching the forum, therefore, it is vital the be written to accurately describe your thread content or overall objective using ONLY search friendly key words.

    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Case Sensitive Conditional Formatting
    By fcasteel in forum Excel General
    Replies: 3
    Last Post: February 25th, 2008, 14:09
  2. Case Sensitive Conditional Formatting
    By Adallia.tyler in forum Excel General
    Replies: 7
    Last Post: August 13th, 2007, 17:22
  3. Case-sensitive Counting
    By simonpnewman in forum Excel General
    Replies: 4
    Last Post: May 4th, 2007, 18:46
  4. Case Sensitive Select Case Loop
    By Et3rnal in forum Excel General
    Replies: 3
    Last Post: July 12th, 2006, 14:35
  5. Case sensitive counts
    By leakylee in forum Excel General
    Replies: 3
    Last Post: July 3rd, 2006, 16:25

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