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.

 

[Cbrine]

=MID(MID(MID(MID(A1,FIND(" ",A1)+1,50),FIND(" ",MID(A1,FIND(" ",A1)+1,50))+1,50),FIND(" ",MID(MID(A1,FIND(" ",A1)+1,50),FIND(" ",MID(A1,FIND(" ",A1)+1,50))+1,50))+1,50),FIND(" ",MID(MID(MID(A1,FIND(" ",A1)+1,50),FIND(" ",MID(A1,FIND(" ",A1)+1,50))+1,50),FIND(" ",MID(MID(A1,FIND(" ",A1)+1,50),FIND(" ",MID(A1,FIND(" ",A1)+1,50))+1,50))+1,50))+1,50)

GOOD GOD there must be an easier way. This formula will find the 4th space and mid string from it.

 

[PaddyD]

yep - there are easier ways! before we go into them, however, what are you actually trying to do?

 

[BuddieB]

I was actually just curious if there was a way to select the Nth occurance of some text in a cell. I have dealt with a bunch of things involving the find command, and I could write some VBA to cycle through the text or search for the Xth case of something, but I was more curious if anyone knew of a formula way to determine the location of the Nth occurance of a text value.

Just trying to dive into pool of knowledge out there with all your XL MVP's ray:

 

[Aladin Akyurek]

I was actually just curious if there was a way to select the Nth occurance of some text in a cell...

Nothing beats morefunc's Wmid...

=WMID(A1,n,1)

where n = 4. The default separator for WMID is a space.

 

[BuddieB]

Thanks Alladin, I'll take a look.

I figured the best way was going to be with a UDF.

 

[Aladin Akyurek]

Thanks Alladin, I'll take a look.

I figured the best way was going to be with a UDF.

Btw:

In A2 enter:

school starts tomorrow at 0900 sharp

In B2 enter a space (using space bar)

In C2 enter: 4

In D2 enter:

=SEARCH(CHAR(127),SUBSTITUTE(A2,B2,CHAR(127),C2))

which gives you 4th space if available.

 

[guybrown]

cheers aladin. i like that... so many years later.

 

[Aladin Akyurek]

cheers aladin. i like that... so many years later.

Great. I appreciate the feedback.

 

[forkman]

Even more years later and I just used the formula.

Thanks Aladin