# How to count occurences of “&nbsp;” in a cell

I was given a spreadsheet containing a bunch of HTML lines sampled below.

``````<nobr>&nbsp;&nbsp;&nbsp;&nbsp;<a href="../Descriptive_Addresses.htm"><b>address: descriptive service location</b></a></nobr><br>
<nobr>&nbsp;&nbsp;&nbsp;&nbsp;<a href="../Acronyms.htm"><b>approved for contacts</b></a></nobr><br>
``````

Each line begins with one to four occurences of `&nbsp;`.

I need to COUNT only the number of occurences of `&nbsp` in each cell; this drives indentation later on.

Using this formula, `=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,"&nbsp;","")))/LEN("&nbsp;")` Excel appears to be counting the instances of ALL spaces, not just those defined by `&nbsp;`. See the example below.

Similar count-substring-within-cell solutions, like this freak out because I'm using reserved characters within my search string.

Any suggestions about how to delineate the two flavors of spaces--`&nbsp;` and a space-bar-space in my formula?

Thanks

-
Possible duplicate: Please check this question & answer. – speakr Jun 14 '12 at 14:10
@speakr good link, but I'll be clarifying my question. It's more about differentiating between a space and &nbsp; in a cell. – dwwilson66 Jun 14 '12 at 14:34
@dwwilson66: Can you please clarify this "Are you looking for number of occurences of &nbsp;" OR only spaces ( ) OR both? – Siva Charan Jun 14 '12 at 17:04
As @SivaCharan says, your formula works to count just the occurrences of `&nbsp`. I'm not sure why you think it's counting spaces as well. – Doug Glancy Jun 15 '12 at 1:52

If you are looking only number of occurences of `&nbsp;` then your formula is working as expected.

``````=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,"&nbsp;","")))/LEN("&nbsp;")
``````

Refer the screenshot:-

-

Well, I'm chalking it up to Excel getting stupid on me. I cut-and-pasted the data to a new spreadsheet, and viola...my results matched @SivaCharan's instead of my wonky results from yesterday.

Thanks for verifying that the formula WAS correct...it forced me to look elsewhere for the problem!

-
if his answer was correct, or helped you find the answer in a substancial way, please mark it as the correct one. – Journeyman Geek Jun 15 '12 at 15:31
Man, you folks are too quick for me, @JourneymanGeek. Your msg came through while I was waiting for a refresh to do just that... :) – dwwilson66 Jun 15 '12 at 15:34
lol. It was an interesting question ;p – Journeyman Geek Jun 15 '12 at 15:36
I just wish there was a real answer. Non-reproducable glitches are the BEST. :) – dwwilson66 Jun 15 '12 at 15:38