finding each blank in a cell

[oldbrewer]

I am trying to find the location of all the blanks in a cell - the cell is woooord1 thru to word12 below
					blank	blank	blank	blank	blank	blank	blank	blank	blank	blank	blank
					1	2	3	4	5	6	7	8	9	10	11
	characters	blanks	words		9	17	17	17	17	17	17	17	17	34	34
woooord1 worddd2 wwwwwwwwwwwword3 word4 word5 word6 word7 word8 word9 word10 word11 word12	90	11	12
formula in first results cell which correctly gives 9 is =FIND(" ",$D$8,FIND(" ",$D$8)+I6-1)
this cell is I7 and has been copied across
the sentence is in D8
the second blank is correctly found
third and subsequent blanks are not found
can anybody tell me why please?

<colgroup><col><col span="2"><col><col><col><col><col span="10"></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

my own A2 is formatted as wrapped text - could that be a factor?

If you forced the text to wraps using Line Feeds (Alt+Enter), rather than letting it wrap naturally, then yes, that would affect the counts because Line Feeds are physical characters (ASCII 10). If you want us to look at your workbook, post a copy of it to one of the free file sharing websites (so we can all look at it). This is a good one to use...

http://www.box.net/files

Remember to note the URL they give you for the file and post that back here for us to use in retrieving the workbook.

 

[oldbrewer]

Just to clarify -- the formula doesn't find the nth blank -- it finds the blank starting after the character position in the cell to the left.

I do not understand what you are saying here. If the cell contains aa bbb cccc ddddd eeee fff gg
I want to find 3 7 12 18 23 27

 

[shg]

	A​	B​	C​	D​	E​	F​	G​	H​
1​	​	​	blank1​	blank2​	blank3​	blank4​	blank5​	blank6​
2​	aa bbb cccc ddddd eeee fff gg		3​	7​	12​	18​	23​	27​

 

[oldbrewer]

thanks again shg - but it does not work for me - yet - I will open a new spreadsheet and start again.

 

[oldbrewer]

rick - just saw your post - I just formatted as wrapped text - I did not use shift enter or whatver the command is.

 

[Rick Rothstein]

rick - just saw your post - I just formatted as wrapped text - I did not use shift enter or whatver the command is.

Just out of curiosity, what answers are you getting for the text shg posted in Message #13?

 

[oldbrewer]

	0	1	2	3	4
aa bbb ccc dddd eeee	3	3	3	7	7
=FIND(" ", $A$3, B2 + 1)
A3 is words
formula is in B3

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

no formula in #13

 

[Rick Rothstein]

	0	1	2	3	4
aa bbb ccc dddd eeee	3	3	3	7	7
=FIND(" ", $A$3, B2 + 1)
A3 is words
formula is in B3

<tbody>
</tbody>

I tried pretty much everything I could think of and I cannot think of anyway to make the formula shg posted output the numbers (especially repeated numbers) that you show. I think you are going to have to post a copy of your workbook so we can see what is going on directly.

 

[Charlie45]

Your formula in B3 is looking on row 2. That doesn't work.
Put your formula in C3 (not B3) and copy across.

Edit:
Change formulas to =FIND(" ", $A$3, B3 + 1)