Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Substitute Function Non-case Sensitive

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 19:10.

  • #2
    Re: Substitute Function Non-case Sensitive

    Can you not just use nested formula?

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

    Cheers
    Andy

    Comment


    • #3
      Re: Substitute Function Non-case Sensitive

      Or =SUBSTITUTE(UPPER(A1),"E","")

      Comment


      • #4
        Re: Substitute Function Non-case Sensitive

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

        Comment


        • #5
          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?

          Comment


          • #6
            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

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X