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.

 

[Rick Rothstein]

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.

Here is another way to write it...

=FIND(",",A2)+FIND(CHAR(8),SUBSTITUTE(MID(A2,FIND(",",A2),99)," ",CHAR(8),2))-1

 

[Aladin Akyurek]

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!

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.

What do we have in B2 and C2?

By the way, we can avoid invoking both SEARCH and FIND in the same formula by adopting just one of them.

The following would also return what you seek: the position of the 2nd space after a comma:

Let A2 house the target string and B2 2.

=FIND(",",A2)+FIND(CHAR(127),SUBSTITUTE(REPLACE(A2,1,FIND(",",A2),"")," ",CHAR(127),B2))

 

[Rick Rothstein]

Actually, in thinking some more about the specific goal of finding the second space after the first comma.... assuming there is always a space after the comma, then it would be considered the first space after the comma... with that said, then this simple formula should calculate the position of the second space after the comma, correct?

=FIND(" ",A2,FIND(",",A2)+2)

 

[gurs]

Thanks for the help gents. Simple is better! All three of these will find the second space after the comma in cell A2 (although only the last two can be modified to find the nth space by changing ',2' to whatever observation you want):

FIND(" ",A2,FIND(",",A2)+2)
FIND(",",A2)+FIND(CHAR(127),SUBSTITUTE(REPLACE(A2,1,FIND(",",A2),"")," ",CHAR(127),2))
SEARCH(CHAR(127),SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND(",",A2))," ",CHAR(127),2))+FIND(",",A2)

 

[Rick Rothstein]

All three of these will find the second space after the comma in cell A2

Which, of course, is what you asked for.

although only the last two can be modified to find the nth space by changing ',2' to whatever observation you want

Which is why a poster should always ask for what they really need and not try to simplify their question... the best solution for the simplified question may not (actually, usually is not) always be extendable to a more general case.

 

[chrisignm]

Hey! I have an algorithm, where I need this formula to search from the right. How could I do that?

Thanks a lot.

 

[Rick Rothstein]

Hey! I have an algorithm, where I need this formula to search from the right. How could I do that?

See my mini-blog article here...

Get "Reversed" Field from Delimited Text String

 

[rohan82]

Thanks great job

 

[rohan82]

Thanks aladin

 

[HankJ]

and I today, many thanks Aladin
Hj