1

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?

Thanks

  • 1
    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
2

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:-

Formula with count

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.