Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Extract Text After Space In String

  1. #1
    New Member
    Join Date
    Jul 2011
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract Text After Space In String

    I need to extract text after space in string e.g.

    INV1019469 Intrum Justitia Oy
    INV1028 Petskiboat Oy
    INV102812 Palo, Tolvanen & Al
    INV103 Fast Capital Oy

    Result:
    Intrum Justitia Oy
    Petskiboat Oy
    Palo, Tolvanen & Al
    Fast Capital Oy

    Thanks
    Lasse
    Last edited by holmbjerg; Jul 7th, 2011 at 04:18 AM. Reason: Need to add some more

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Extract Text After Space In String

    Try

    =RIGHT(A1,LEN(A1)-FIND(" ",A1))
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract Text After Space In String

    Hi

    Formula wise perhaps:

    =MID(A1,FIND(" ",A1&" ")+1,2^15)
    Richard Schollar

    Using xl2013

  4. #4
    New Member
    Join Date
    Jul 2011
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After Space In String

    Instr function is your friend for this:

    InStr returns the position of the first occurance of a string inside another string

    so:

    Code:
    Instr("INV1019469 Intrum Justitia Oy", " ")
    will return 11

    you can nest this inside another function or assign it as a variable

    Code:
     
    dim fooStr as string
    dim cutDownStr
     
    cutDownStr = "INV1019469 Intrum Justitia Oy"
     
    fooStr = right(cutDownStr, (len(cutDownStr)-inStr(cutDownStr, " "))
    Len finds the lenght of the string and Right takes the right part of the string up to the number of characters in the second argument

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,519
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extract Text After Space In String

    Hi Lasse

    And also:

    =REPLACE(A1,1,FIND(" ",A1),"")
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  6. #6
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    2,834
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After Space In String

    UDF
    Code:
    Function ExtractText(Str As String) As String
        With CreateObject("VBScript.RegExp")
            .Pattern = "\b(?!\w*\d+)([A-Za-z]+\s*)+"
            ExtractText = .Execute(Str)(0)
        End With
    End Function

  7. #7
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,519
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extract Text After Space In String

    With ZPhantom's example, also:

    Code:
     
    Dim fooStr As String
    Dim cutDownStr
     
    cutDownStr = "INV1019469 Intrum Justitia Oy"
    fooStr = Mid(cutDownStr, 1 + InStr(cutDownStr, " "))
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  8. #8
    New Member
    Join Date
    Jul 2011
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After Space In String

    Thanks for your quick responds

    It works perfect

    Take care
    Lasse

  9. #9
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    2,834
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After Space In String

    Adjusted to examples:
    Code:
    Function ExtractText(Str As String) As String
        With CreateObject("VBScript.RegExp")
            .Pattern = "\b(?!\w*\d+).+"
            ExtractText = Trim(.Execute(Str)(0))
        End With
    End Function

  10. #10
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    1,049
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text After Space In String

    Hi Vog Sir..I do have same query related to this..
    Instead, entire Right part, I need some specific text..
    example..
    UTS-COVPG 7-03
    I need only 7..how do i achieve this..

    Quote Originally Posted by VoG View Post
    Try

    =RIGHT(A1,LEN(A1)-FIND(" ",A1))

Some videos you may like

User Tag List

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
  •