Formula - Find the nth Space in a Text String

[BuddieB]

Is there a way to find the Nth space in a text string:

For example MrExcel Message Board Forum Index

If I wanted the 3rd space between Board and Forum, to help me determine the start of the 4th word.

Is there a formula that lets me select which space I want? I know how to find the first space using the find (" ", A1) command, but dont know how to find the 2nd, 3rd or 4th spaces...

I would like to do this w/o VBA.

 

[Aladin Akyurek]

and I today, many thanks Aladin
Hj

You are welcome.

 

[TryingToLearn]

LOL, let's add another 4 years to the usefulness of this thread! That's 16 and counting.

Thanks Aladin

 

[Aladin Akyurek]

LOL, let's add another 4 years to the usefulness of this thread! That's 16 and counting.

Thanks Aladin

Happy to hear that

 

[Vincent Khandagale]

pls add 4 more years! it is still the best solution to this very old problem !
P.S. I regiestered on the site just to post this comment

Thanks Aladin!

 

[Scott Huish]

The OP originally wanted to know the position of the space to find the next word, TEXTSPLIT can be used to accomplish that now.

Book1
	A	B	C
1	Sentence	Word #	Word
2	The quick brown fox jumped over the lazy dog	4	fox
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INDEX(TEXTSPLIT(A2," "),B2)