How to Handle the #VALUE! Error when Comparing Cells (One word / No Space after word)?

This I think should be easy for any pro. I tried searching online but couldn't find the answer.

The following formula below is what I am using to compare the first word of two cells. However, if one cell doesn't have two words, meaning there won't be a space after the word, I get the #VALUE! error instead of the false portion of the formula being executed. How can I fix this?

Please Login or Register  to view this content.

Originally Posted by Glenn Kennedy

On reflection, it might be "safer" to use this:

=IF(AND(ISNUMBER(SEARCH(" ",TRIM(B1))),ISNUMBER(SEARCH(" ",TRIM(B2)))),"Same","Diff")

which will eliminate stray leading/trailing spaces.

Thanks, but this only works if I am either 1) applying it to the cell reference that I know will give the error (in my case it's only the header / B1) or 2) or dealing with cells that only have numbers.

I am comparing text.

Originally Posted by davesexcel

You would get the error if no space is found,

Formula:

Please Login or Register  to view this content.

if no space in B1 then you will get the error. The search function is telling you where the space is, if no space, it can't give you a value.

Formula:

Please Login or Register  to view this content.

I was just about getting ready to update this to show that I found out that one can use the IFERROR statement, when I saw you had already posted this response.

Thank you.

Maybe?

=IF(LEFT(B2,SEARCH(" ",B2&" ",1)-1)<>LEFT(B1,SEARCH(" ",B1&" ",1)-1),"Diff","Same")

Originally Posted by Glenn Kennedy

See the attached file.

I think that YOU need to post a sample file, showing a range of expected results.

I simply applied the ISNUMBER function to one portion (the portion that looks at the cell with only one word) and it worked. I then applied it to the second portion and it didn't. Now upon studying this further, I see that you swapped the positions of the "DIFF" and "SAME" in your formula. When I do that, I then get the correct results.

I have attached a file showing the results.