Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

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

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

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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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