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

1. 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. Re: Search cell for string plus space

Remove the *
=ISNUMBER(SEARCH("gr ",A2))

3. Re: Search cell for string plus space

Originally Posted by Fluff
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)

4. Re: Search cell for string plus space

It looks like this works

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

5. Re: Search cell for string plus space

Originally Posted by Okoth
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.

6. Re: Search cell for string plus space

Originally Posted by Rick Rothstein
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