Consulting

Results 1 to 9 of 9

Thread: Searching for special characters

  1. #1
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,130
    Location

    Searching for special characters

    This question is for all the excel guru's. The following formula searches for special characters in a string.... or so the guy said. How and why does it work?

    =SUMPRODUCT(--ISNUMBER(SEARCH({"!","@","#","$","%","^","&","~*","(",")","_","+","=","{","}","[","]","|","\",":",";","'","""","<",">",",",".","~?","/","~~","`"}, A1)))>0
    I can picture geargiboy and P45cal salivating at the thought of getting into this one.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,239
    Location
    The best way to understand it is to break it down and look at the results we get at each step.

    Let's say we shorten the array of special characters just to make is shorter for the explanation, to:
    =SUMPRODUCT(--ISNUMBER(SEARCH({"!","@","#"}, A1)))>0

    Now lets say that in cell A1 is the string:
    Sexy! George#

    --------------------------------------------------------------------------------------------------
    If you test it with the search function alone:
    =SEARCH({"!","@","#"}, A1)

    You will get:
    5, #VALUE!, 13

    This is because the characters in the array are found in position: 5 and 15, the error is due to the middle character of the array not being found.
    You get an array of the results of the SEARCH function for each of the items in the array.
    ! is found in position 5, @ is not found, # is found in position 13


    --------------------------------------------------------------------------------------------------
    If you test with SEARCH and ISNUMBER:
    =ISNUMBER(SEARCH({"!","@","#"}, A1))

    You will get:
    TRUE, FALSE, TRUE

    This is because the ISNUMBER function will return TRUE, FALSE depending on whether the value is a number or not, so if you take the result (above) from the SEARCH alone, the first and last values are numbers and the middle value is an error so this is why you get TRUE, FALSE, TRUE.


    --------------------------------------------------------------------------------------------------
    If you test with SEARCH and ISNUMBER and the double negative:
    =--ISNUMBER(SEARCH({"!","@","#"}, A1))

    You will get:
    1,0,1

    The double negative operator (--), when applied to a logical value (TRUE or FALSE), converts TRUE to 1 and FALSE to 0. So, it effectively converts the array of TRUE/FALSE values returned by the ISNUMBER function to an array of 1s and 0s. It also converts numbers stored as text back to numerical values.


    --------------------------------------------------------------------------------------------------
    Adding the SUMPRODUCT or SUM:
    =SUMPRODUCT(--ISNUMBER(SEARCH({"!","@","#"}, A1)))

    You will get:
    2

    this is because quite simply: the sum of 1,0,1 is 2.


    --------------------------------------------------------------------------------------------------
    Adding in the >0 makes it a comparison to zero, this means if the result of the sum is zero then then final formula result is FALSE, whereas if the result of the final formula is greater than zero then the result is TRUE
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  3. #3
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,130
    Location
    Excellent explanation. Now I can be reasonably confident I wont get busted by ASIO for trying to launch rockets.


    So.... everything between the first and last curly brackets is simply special characters being defined?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,736
    Location
    FWIW, you could just use COUNT instead of SUMPRODUCT(--ISNUMBER(...)) since COUNT ignores errors anyway:

    =COUNT(SEARCH({"!","@","#","$","%","^","&","~*","(",")","_","+","=","{","}" ,"[","]","|","",":",";","'","""","<",">",",",".","~?","/","~~","`"}, A1))>0
    Be as you wish to seem

  5. #5
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,239
    Location
    Quote Originally Posted by Aussiebear View Post
    So.... everything between the first and last curly brackets is simply special characters being defined?
    Yes the below:
    {"!","@","#","$","%","^","&","~*","(",")","_","+","=","{","}","[","]","|","",":",";","'","""","<",">",",",".","~?","/","~~","`"}

    Is simply an array of values, the curly brackets denote an array.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  6. #6
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,130
    Location
    So why do some characters need a "~" to precede them?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,239
    Location
    In Excel formulas, the tilde is used as an escape character. It tells Excel to treat the following character as a literal character rather than as a part of a special character sequence.


    The tilde is used before certain characters like "*", "?", and "~" itself. This is because these characters have special meanings in the SEARCH function. For example:


    "*" is a wildcard that matches any sequence of characters.
    "?" is a wildcard that matches any single character.
    "~" itself is used as an escape character in SEARCH.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  8. #8
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,736
    Location
    Given the nature of all the special characters, you could also use FIND instead of SEARCH and then omit the additional tildes.
    Be as you wish to seem

  9. #9
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,239
    Location
    Quote Originally Posted by Aflatoon View Post
    GFWIW, you could just use COUNT instead of SUMPRODUCT(--ISNUMBER(...)) since COUNT ignores errors anyway
    Quote Originally Posted by Aflatoon View Post
    Given the nature of all the special characters, you could also use FIND instead of SEARCH and then omit the additional tildes.
    I agree, the original formula is not optimal.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

Tags for this Thread

Posting Permissions

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