• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract first two words from the string with VBA

rumshar

Member
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
 
Last edited by a moderator:
Code:
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

Code:
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)
 
Last edited:
Will your string always have two spaces?

Code:
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
 
Code:
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

Code:
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]
 
Back
Top