CODEWITHSUNDEEP
excel
john parker
john parker

Reputation: 23

Check to see if cell contains 2 spaces directly after each other

Is there a way that I can check to see if my cell contains 2 spaces immediately after one another?

For example if my cell contained "The cat went meow" then my formula below will return "No" because there is only one space between each character. However, if there where 2 spaces like

"The cat  went meow"

then the formula would return "Yes".

I have tried the following formula but it picks up all the spaces instead of what I want it to do. Can someone please show me how i could correct this?

=IF(ISNUMBER(SEARCH(" " & " ",B1)),"Yes","no")

Upvotes: 0

Views: 5541

Answers (3)

pnuts
pnuts

Reputation: 59485

Might be worth considering TRIM():

=LEN(B1)=LEN(TRIM(B1))

Test cases:

"The cat went meow"    TRUE    (single space)
"The cat  went meow"   FALSE   (double space)
" The cat went meow"   FALSE   (leading space)
"The cat went meow "   FALSE   (trailing space)

this works for your example, if allowance is made for returning T/F rather than Y/N (to keep the formula short - Y/N could be arranged).

A better (shorter) version was offered by @Rick Hitchcock in a comment:

=B1=TRIM(B1)

However it would return FALSE not only for "double" spaces but any quantity of spaces that are not on their own (immediately next to characters on both sides)

and

it would return FALSE even for single spaces if at the start and/or end of your string.

So this is not exactly what you asked for, and no more, but in practice seems likely to be more useful in general.

Upvotes: 3

Rick Hitchcock
Rick Hitchcock

Reputation: 35670

Your code works for me as-is. You could simplify it like this:

=IF(ISNUMBER(SEARCH("  ",B1)),"Yes","no")

To avoid error-checking, you could compare the string to a version with double-spaces converted to spaces, like this:

=IF(SUBSTITUTE(B1,"  "," ")<>B1,"Yes","no")

But I'm unsure what problem you're having with your existing code.

Upvotes: 1

Jur Pertin
Jur Pertin

Reputation: 564

Try this

    =IF(ISERROR(FIND("  ",B1,1)),"No","Yes")

or 

    =IF(ISERROR(SEARCH("  ",B1,1)),"No","Yes")

Upvotes: 0

Related Questions