Page 1 of 3 123 LastLast
Results 1 to 10 of 25
Like Tree1Likes

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. #1
    Board Regular
    Join Date
    Aug 2003
    Location
    Boston
    Posts
    174

    Default 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. #2
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,191

    Default 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.
    Xcelerated Solutions
    Office Automation Solutions for the Toronto Area

  3. #3
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default 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. #4
    Board Regular
    Join Date
    Aug 2003
    Location
    Boston
    Posts
    174

    Default 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. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,817

    Default Re: Formula - Find the nth Space in a Text String

    Quote 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.
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    Board Regular
    Join Date
    Aug 2003
    Location
    Boston
    Posts
    174

    Default 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. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,817

    Default Re: Formula - Find the nth Space in a Text String

    Quote 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.
    Superstar31 likes this.

  8. #8
    Board Regular
    Join Date
    Jul 2008
    Posts
    100

    Default Re: Formula - Find the nth Space in a Text String

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

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,817

    Default Re: Formula - Find the nth Space in a Text String

    Quote Originally Posted by guybrown View Post
    cheers aladin. i like that... so many years later.
    Great. I appreciate the feedback.
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    New Member
    Join Date
    May 2012
    Posts
    1

    Default Re: Formula - Find the nth Space in a Text String

    Even more years later and I just used the formula.

    Thanks Aladin

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com