Announcement

Collapse
No announcement yet.

Extract Nth Word from text in cell

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Extract Nth Word from text in cell



    Respected sir,

    I ran with my legs - extract "with"
    I have big cat - extract "big"

    both words are extracted after two spaces

    i want to have my house - Extract "have"
    i want to dance in my house - Extract "dance"

    Above both words are extract after three spaces

    how do i do above

    regards

    juzar para

  • #2
    Re: Extract word from text in cell

    Suggest doing a little search before posting for help.

    Extract Nth Word From Sentence

    The following example extracts the word after 3 spaces

    =MID(MID(MID(SUBSTITUTE(A6," ","^",3),1,256),FIND("^",SUBSTITUTE(A6," ","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A6," ","^",3),1,256),FIND("^",SUBSTITUTE(A6," ","^",3)),256))-2)
    Cheers,

    S M C

    Click To Read: How To Use Tags In Your Threads/Posts
    Please take time to read Forum Rules before posting
    Message To Cross Posters

    Comment


    • #3
      Re: Extract Nth Word from text in cell

      Friend,

      for word after two spaces

      please reply

      Comment


      • #4
        Re: Extract Nth Word from text in cell

        Please replace 3 with 2 in the above formula!!!
        Cheers,

        S M C

        Click To Read: How To Use Tags In Your Threads/Posts
        Please take time to read Forum Rules before posting
        Message To Cross Posters

        Comment


        • #5
          Re: Extract Nth Word from text in cell

          With text in A2 and "n" in B2 (a number), try this formula in C2 to extract the nth word

          =TRIM(RIGHT(SUBSTITUTE(LEFT(TRIM(A2),FIND("^^",SUBSTITUTE(TRIM(A2)&" "," ","^^",B2))-1)," ",REPT(" ",99)),99))

          Comment


          • #6


            Re: Extract Nth Word from text in cell

            parajf,

            If A1 contains:
            I ran with my legs

            And, you want the third word or number from a text string separated by a space or spaces;
            In B1 enter:
            =GetTN(A1, 3)

            And, the result will be:
            with



            Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

            1. Copy the below code/function, by highlighting the code and pressing the keys CTRL + C
            2. Open your workbook
            3. Press the keys ALT + F11 to open the Visual Basic Editor
            4. Press the keys ALT + I to activate the Insert menu
            5. Press M to insert a Standard Module
            6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
            7. Press the keys ALT + Q to exit the Editor, and return to Excel


            Code:
             
            Option Explicit
            Function GetTN(ByRef TNs As String, ByVal Position As Integer) As String
            ' Get 1st 2nd 3rd... word or number from a string of text and numbers separated by a space or spaces.
            ' Author:  Stanley D. Grom, Jr., stanleydgromjr at ExcelForum.com, hiker95 at MrExcel.com, Stanley D. Grom at Ozgrid.com
            ' Updated: September 03, 2010
            '
            ' A1 is equal to (without the " marks): "Aa 11 Bb 22 Cc 33Dd Ee44"
            ' B1: =GetTN(A1, 5)
            ' The result is "Cc"
            '
            ' =GetTN(A1,Column()-1)
            '
            'Modification from:
            'Function GetClaim(ByVal Claims As Range, ByVal Position As Integer) As String
            'Function GetClaim(ByVal Claims As String, ByVal Position As Integer) As String
            'Author:  Leith Ross
            'http://www.excelforum.com/excel-programming/744090-string-manipulation-split-into-multiple-variables.html
            '
            Dim Cnt As Integer
            Dim Matches As Object
            Dim RegExp As Object
            Dim S As String, Text As String
            Application.Volatile
            Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.Global = True
            RegExp.IgnoreCase = True
            RegExp.Pattern = "\s*(\S+)\s+(.*)"
            Text = TNs & " "
            Do While RegExp.Test(Text)
              S = RegExp.Replace(Text, "$1")
              Text = RegExp.Replace(Text, "$2")
              Cnt = Cnt + 1
              If Cnt = Position Then GetTN = S
            Loop
            End Function

            Have a great day,
            Stan

            Comment

            Working...
            X