# Formula - Find the nth Space in a Text String

This is a discussion on Formula - Find the nth Space in a Text String within the Excel Questions forums, part of the Question Forums category; Is there a way to find the Nth space in a text string: For example MrExcel Message Board Forum Index ...

1. ## Formula - Find the nth Space in a Text String

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.

2. ## Re: Formula - Find the nth Space in a Text String

=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.

3. ## Re: Formula - Find the nth Space in a Text String

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

4. ## Re: Formula - Find the nth Space in a Text String

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

5. ## Re: Formula - Find the nth Space in a Text String

Originally Posted by BuddieB
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.

6. ## Re: Formula - Find the nth Space in a Text String

Thanks Alladin, I'll take a look.

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

7. ## Re: Formula - Find the nth Space in a Text String

Originally Posted by BuddieB
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.

8. ## Re: Formula - Find the nth Space in a Text String

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

9. ## Re: Formula - Find the nth Space in a Text String

Originally Posted by guybrown
cheers aladin. i like that... so many years later.
Great. I appreciate the feedback.

10. ## Re: Formula - Find the nth Space in a Text String

Even more years later and I just used the formula.

Page 1 of 4 123 ... Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•