Formula - Find the nth Space in a Text String

BuddieB

Board Regular
Joined
Aug 6, 2003
Messages
174
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
=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.
 
Upvote 0
yep - there are easier ways! before we go into them, however, what are you actually trying to do?
 
Upvote 0
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 :pray:
 
Upvote 0
BuddieB said:
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.
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top