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]

Even more years later and I just used the formula.

Thanks Aladin

Great. Thanks for providing feedback.

 

[0netrack]

Thanks Aladin - that formula has just been very useful for me too.

 

[ramananhrm]

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)))

 

[RayLewis]

The gift that keeps on giving, thanks Aladin!

 

[Aladin Akyurek]

The gift that keeps on giving, thanks Aladin!

Thanks for the kind feedback.

 

[Rick Rothstein]

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

 

[richarddec]

This has been helping people for years! Thanks!

 

[Aladin Akyurek]

This has been helping people for years! Thanks!

Happy to hear such. Thanks for the kind feedback.

 

[gurs]

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!

 

[gurs]

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.