finding each blank in a cell

shg

MrExcel MVP

Joined

May 7, 2008

Messages

21,836

Office Version

1. 2010

Platform

1. Windows

• Nov 21, 2013

• #2

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

1​

blank 1

blank 2

blank 3

blank 4

blank 5

blank 6

blank 7

blank 8

blank 9

blank 10

blank 11

2​

9​

17​

34​

40​

46​

52​

58​

64​

70​

77​

84​

3​

woooord1 worddd2 wwwwwwwwwwwword3 word4 word5 word6 word7 word8 word9 word10 word11 word12

In C2 and copied right,

=FIND(" ", $A$3, B2 + 1)

 

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• Nov 21, 2013

• #3

many thanks shg, but why did it find the first two correctly, please ?

 

Upvote 0

shg

MrExcel MVP

Joined

May 7, 2008

Messages

21,836

Office Version

1. 2010

Platform

1. Windows

• Nov 21, 2013

• #4

I didn't save the layout, sorry. I expect because it keeps looking in the same place. Watch it evaluate where it returns the first bad value.

 

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• Nov 21, 2013

• #5

shg - I put your formula in and it still gives wrong answers

blank	blank	blank
1	2	3

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

9 9 9

 

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• Nov 21, 2013

• #6

I have even replaced the last bit of the formula with 2 3 4 etc rather than a cell reference

 

Upvote 0

shg

MrExcel MVP

Joined

May 7, 2008

Messages

21,836

Office Version

1. 2010

Platform

1. Windows

• Nov 21, 2013

• #7

	A​	B​	C​	D​	E​	F​
1​	​	​	blank1​	blank2​	blank3​	​
2​	9 9 9 9		2​	4​	6​	C2 and across: =FIND(" ", $A$2, B2 + 1)

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,458

Office Version

1. 365

Platform

1. Windows

• Nov 21, 2013

• #8

oldbrewer said:

shg - I put your formula in and it still gives wrong answers

blank	blank	blank
1	2	3

<tbody>
</tbody>

9 9 9

Click to expand...

Not sure what you are doing differently, but when I try shg's formula, in C2 and copied across (text string in A3), it works fine for me.

 

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• Nov 21, 2013

• #9

my own A2 is formatted as wrapped text - could that be a factor? I have double checked the formula and that it drags across correctly - happy to send the spreadsheet to you if it would help !

 

Upvote 0

shg

MrExcel MVP

Joined

May 7, 2008

Messages

21,836

Office Version

1. 2010

Platform

1. Windows

• Nov 21, 2013

• #10

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.

 

Upvote 0