Excel LEN, SEARCH and FIND Double Space Bug?
<p>I am attempting to add conditional formatting to an Excel 2010 worksheet and having trouble with one of the formulas involved.  From what I'm able to determine Excel is counting a double space as 1 character, and double spaces are what I'm trying to identify!</p>

<p>This is my worksheet, with notations for the purpose of illustration:</p>

<p><a href="https://i.stack.imgur.com/k0Zde.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/k0Zde.png" alt="Snip of Excel worksheet showing formulas in use"></a></p>

<p>It is hard to see, but the string in cell <code>B45</code> has a double-space between "1XXX" and "TRUCK".  <code>B46</code> is the exact same string, but with only a single space in the same position.  The formulas for cells <code>M45:N46</code> are annotated with the result showing.</p>

<p>The issue is why the two formula results are equal when the <code>SUBSTITUTE</code> should replace the double space with a single space?  I've tried using <code>SEARCH</code> and <code>FIND</code> for "  " (double space) with similar results.</p>

<p>I started out using a comparison between the two values for my conditional formatting to determine whether a cell contained a double space, but with this issue I have been unable to complete this simple task!  Any assistance would be appreciated.</p>