Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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="../State_or_Possession_Postal_Abbreviations.htm"><b>address: state</b></a></nobr><br>
<nobr>&nbsp;&nbsp;&nbsp;&nbsp;<a href="../Street_Abbreviations.htm"><b>address: street</b></a></nobr><br>
<nobr>&nbsp;&nbsp;&nbsp;&nbsp;<a href="../Acronyms.htm"><b>approved for contacts</b></a></nobr><br>
<nobr>&nbsp;&nbsp;&nbsp;&nbsp;<a href="../Military_Addresses.htm"><b>armed forces</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.

My results

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?


share|improve this question
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.


Refer the screenshot:-

Formula with count

share|improve this answer
up vote 0 down vote accepted

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

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

share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .