Search cell for string plus space
Results 1 to 6 of 6

Thread: Search cell for string plus space
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Search cell for string plus space

    I want to check if the value of a cell is written as 100 gram (or a different number) instead of 100gr.

    Example:
    100gr white sugar is wrong
    100 gram white sugar is right

    or

    38gr cinnamon is wrong
    38 gram cinnamon is right

    I have tried several formulas but I'm always stuck with the space.

    =SEARCH("gr *", C18 ) doesn't work
    =COUNTIF(C18,"*gr *")>0 also doesn't work
    =ISNUMBER(SEARCH("gr *",B18)) also not

    What formula can I use to check this?

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,519
    Post Thanks / Like
    Mentioned
    444 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Search cell for string plus space

    Remove the *
    =ISNUMBER(SEARCH("gr ",A2))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Sep 2009
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search cell for string plus space

    Quote Originally Posted by Fluff View Post
    Remove the *
    =ISNUMBER(SEARCH("gr ",A2))
    Sorry, but I have tried this before and again and it gives a FALSE.

    Another example of what I don't want is

    100 gr white sugar (is wrong)
    100 gram white sugar (is right)
    Last edited by Okoth; Jul 6th, 2019 at 05:50 PM.

  4. #4
    Board Regular
    Join Date
    Sep 2009
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search cell for string plus space

    It looks like this works

    Code:
    =SEARCH("gr"&CHAR(160),A2)

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,968
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Search cell for string plus space

    Quote Originally Posted by Okoth View Post
    It looks like this works

    Code:
    =SEARCH("gr"&CHAR(160),A2)
    Just so you know... CHAR(160) is not a "normal" space, rather, it is a "non-breaking" space mostly used on websites to keep two words with a space between them together so they do not get split onto two different lines. You have to be careful of these when you copy text from a webpage.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular
    Join Date
    Sep 2009
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search cell for string plus space

    Quote Originally Posted by Rick Rothstein View Post
    Just so you know... CHAR(160) is not a "normal" space, rather, it is a "non-breaking" space mostly used on websites to keep two words with a space between them together so they do not get split onto two different lines. You have to be careful of these when you copy text from a webpage.
    Thanks for the info, Rick

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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