Extract Text After Space In String

[holmbjerg]

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

 

[VoG]

Try

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

 

[Richard Schollar]

Hi

Formula wise perhaps:

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

 

[ZPhantom]

Instr function is your friend for this:

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

so:

Instr("INV1019469 Intrum Justitia Oy", " ")

will return 11

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

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

 

[pgc01]

Hi Lasse

And also:

=REPLACE(A1,1,FIND(" ",A1),"")

 

[Sektor]

UDF

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

 

[pgc01]

With ZPhantom's example, also:

Dim fooStr As String
Dim cutDownStr
 
cutDownStr = "INV1019469 Intrum Justitia Oy"
fooStr = Mid(cutDownStr, 1 + InStr(cutDownStr, " "))

 

[holmbjerg]

Thanks for your quick responds

It works perfect

Take care
Lasse

 

[Sektor]

Adjusted to examples:

[COLOR="Blue"]Function[/COLOR] ExtractText(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "\b(?!\w*\d+).+"
        ExtractText = Trim(.Execute(Str)(0))
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[VBABEGINER]

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

Try

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