Code to Return First 3 words in a string

[AlexSlivi]

Just to make this simple, let's say I have information such as "The dog ran very fast" or "The cow jumped over the moon" but I want to make another column with information such as "The dog ran" or the "The cow jumped", etc. How would I go about doing this? I can then apply your answer to my own situation, as what I have to do is slightly more complicated. Thank you.

 

[prajul89]

=LEFT(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1)-1)

A1 has you Input sentence.

see if this works

 

[Jonmo1]

Just to make this simple, let's say I have information such as "The dog ran very fast" or "The cow jumped over the moon" but I want to make another column with information such as "The dog ran" or the "The cow jumped", etc. How would I go about doing this? I can then apply your answer to my own situation, as what I have to do is slightly more complicated. Thank you.

I highly recommend posting your ACTUAL more complicated situation to begin with.
Often solutions are tailored specifically for the situation described, and cannot be easily modified...

However, this will do what you described.

=LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",3))-1)

The Red 3 represents how many words you want to return.
Assuming each word is seperated by a space.

Hope it helps.

 

[AlexSlivi]

VBA didn't know the search or substitute sub/function? Do I need to define those?

The actual situation is something like...

The product name will be something like:
Flex Max 14 PTP C, Flex Max 7 MA, Valor II MA, Icon I MAS, Legend I PTP CR, etc.)

So...If it's Flex Max #, I need it to return the first three words, where the third word is actually a number. If it's not, I only need the first two words.

 

[prajul89]

Is it a formula you are looking for or VB codes?

 

[AlexSlivi]

VBA code

 

[Jonmo1]

OK, so you want VBA solution.
If the 3rd word is numeric, return first 3 words, else first 2 words.
Right?

Try something like

Sub test()
Dim MyVal As String, x As Variant, Rslt As String
MyVal = "Flex Max 14 PTP C"
x = Split(MyVal)
If IsNumeric(x(2)) Then
    Rslt = Left(MyVal, InStr(1, MyVal, x(2)) + Len(x(2)) - 1)
Else
    Rslt = Left(MyVal, InStr(1, MyVal, x(1)) + Len(x(1)) - 1)
End If
MsgBox Rslt
End Sub

 

[prajul89]

If its in VBA then this should work for you

ANS = Left(Cells(1, 1).Value, Application.WorksheetFunction.Search(" ", Cells(1, 1).Value, Application.WorksheetFunction.Search(" ", Cells(1, 1).Value, Application.WorksheetFunction.Search(" ", Cells(1, 1).Value, 1) + 1) + 1) - 1)

 

[AlexSlivi]

Okay thank you both. I'm sure one of the two will work.