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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You can mention the nth space as you wish.
Eg: -0 or -1 or -2 etc

=LEFT(A1,SEARCH(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-0)))
 
Last edited:
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.
You might find these mini-blog articles of mine to be of some interest then...

Get Field from Delimited Text String

Get "Reversed" Field from Delimited Text String
 
Upvote 0
Thought I would bring this thread back from the dead yet again! Aladin, I have simplified your formula to the following, which successfully finds the second space in field A2:

SEARCH(CHAR(127),SUBSTITUTE(A2," ",CHAR(127),2))

So if field A2 contains "Here is a test phrase, with a comma", the formula above returns 8, which is correct. But what I really need is to find the second space after the first comma (character 28 in the example text). Since the SEARCH function has an option to enter the starting position, I thought it would be easy to modify your formula to do the job, as follows:

SEARCH(CHAR(127),SUBSTITUTE(A2," ",CHAR(127),2),FIND(",",A2))

But unfortunately, this formula returns the #VALUE! error. I think I see why my simplistic approach doesn't work, since the SUBSTITUTE function is replacing the 2nd space without reference to the placement of the comma, therefore the SEARCH function comes up empty when it searches for the replacement character after the comma. The question is, how can I accomplish my objective?

Thanks!
 
Upvote 0
The question is, how can I accomplish my objective?

As soon as I posted my last message the solution dawned on me. I just needed to move the comma locator inside the SUBSTITUTE function, and then add back the location of the comma:

SEARCH(CHAR(127),SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND(",",A2)),B2,CHAR(127),C2))+FIND(",",A2)

Works like a charm! Hope that helps someone at some point.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

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