Extract first two words from the string with VBA

[rumshar]

Hi,
I want to extract first two words from the string with VBA. Can anyone help?

VBA equivalent of =LEFT(A2,FIND(" ",A2,FIND(" ",A2)+1)-1)

With Regards
Rudra

 

[Hui]

Dim myStr As String
Dim Result As String

myStr = [A2]
Result = Left(myStr, InStr(InStr(1, myStr, " ") + 1, myStr, " ", vbTextCompare)-1)

or as a VBA Function

Function First2Words(myStr As Variant) As Variant
  First2Words = Left(myStr, InStr(InStr(1, myStr, " ") + 1, myStr, " ", vbTextCompare) - 1)
End Function

as a function in Excel use =First2Words(A2)

 

[shrivallabha]

Will your string always have two spaces?

Dim strInputString as string
Dim vData As Variant
vData = Split(strInputString & "  ", " ")
MsgBox Trim(vData(0) & " " & vData(1))

 

[rumshar]

Will your string always have two spaces?

Dim strInputString as string
Dim vData As Variant
vData = Split(strInputString & "  ", " ")
MsgBox Trim(vData(0) & " " & vData(1))

@ Hui,
Thank you very much....I really appreciate it.
@ Shrivallabha yes..my string will have always more than 1 space.thanks for your coding help....

With Regards
Rudra

 

[Megna]

Dim myStr As String
Dim Result As String

myStr = [A2]
Result = Left(myStr, InStr(InStr(1, myStr, " ") + 1, myStr, " ", vbTextCompare)-1)

or as a VBA Function

Function First2Words(myStr As Variant) As Variant
  First2Words = Left(myStr, InStr(InStr(1, myStr, " ") + 1, myStr, " ", vbTextCompare) - 1)
End Function

as a function in Excel use =First2Words(A2)

Hello Hui,

Just an curious question, how to get syntax highlighted by putting code in CODE & /CODE tags. Is it inbuilt feature of Xen foro or you have added any addon.
Actually i am also looking for this features in my Xen Foro forum .
Appreciate if you could help me on this.

Thanks
Megna[/CODE]