If you have several words entered into a cell is there a formula that I can
use in another cell that will return the third word in the orignial cell. The
words are always capitalized and separated with a space.

birdgirl31 wrote...
If you have several words entered into a cell is there a formula that I can
use in another cell that will return the third word in the orignial cell. The
words are always capitalized and separated with a space.

If you could use add-ins, Laurent Longre's free MOREFUNC.XLL add-in
provides a function named WMID which is the simplest way to do this.

If you can't use add-ins, to find the k_th space-separated word in x
try

=REPLACE(LEFT(TRIM(x),FIND(CHAR(127),SUBSTITUTE(TR IM(x)&" "," ",CHAR
(127),k))-1),
1,IF(k1,FIND(CHAR(127),SUBSTITUTE(TRIM(x)," ",CHAR(127),k-1)),0),"")

=MID(D17,SEARCH(" ",D17&" ",SEARCH(" ",D17&" ",1)+1)+1,SEARCH(" ",D17 & "
",SEARCH(" ",D17&" ",SEARCH(" ",D17&" ",1)+1)+1)-SEARCH(" ",D17&" ",SEARCH("
",D17&" ",1)+1)-1)

If this post helps click Yes
---------------
Jacob Skaria


"birdgirl31" wrote:

If you have several words entered into a cell is there a formula that I can
use in another cell that will return the third word in the orignial cell. The
words are always capitalized and separated with a space.

The below forumla will only return the third word. If you are looking for a
user defined function please find the below. To use this launch VBE using
ALT+F11 . Insert Module and paste the below function. Save and get back to
workbook.
A1 = "This is a test"

B1 = SecondorThirdWord(A1)

Function SecondorThirdWord(varTemp)
If InStr(varTemp, " ") = 0 Then Exit Function
arrTemp = Split(varTemp, " ")
If UBound(arrTemp) 1 Then
SecondorThirdWord = arrTemp(2)
Else
SecondorThirdWord = arrTemp(1)
End If
End Function

--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

=MID(D17,SEARCH(" ",D17&" ",SEARCH(" ",D17&" ",1)+1)+1,SEARCH(" ",D17 & "
",SEARCH(" ",D17&" ",SEARCH(" ",D17&" ",1)+1)+1)-SEARCH(" ",D17&" ",SEARCH("
",D17&" ",1)+1)-1)

If this post helps click Yes
---------------
Jacob Skaria


"birdgirl31" wrote:

If you have several words entered into a cell is there a formula that I can
use in another cell that will return the third word in the orignial cell. The
words are always capitalized and separated with a space.

Jacob Skaria wrote...
....
Function SecondorThirdWord(varTemp)
If InStr(varTemp, " ") = 0 Then Exit Function
arrTemp = Split(varTemp, " ")
If UBound(arrTemp) 1 Then
SecondorThirdWord = arrTemp(2)
Else
SecondorThirdWord = arrTemp(1)
End If
End Function
....

What a nearly useless udf!

All it takes to make this general is adding a second argument to the
UDF to allow the user to specify which word to return.


Function w(s As String, k As Long) As Variant
Dim v As Variant

k = k - 1 'Split returns 0-based arrays, but this udf uses 1-based
v = Split(Application.WorksheetFunction.Trim(s), " ")

If LBound(v) <= k And k <= UBound(v) _
Then w = v(k) _
Else w = CVErr(xlErrValue)

Erase v
End Function

Dear Harlan

You are right. I have modified to return the word specified.

Just one thing TRIM() function is a VBScript function which is also a
worksheet function. So you can directly call TRIM..

Function GetWord(varTemp As String, lngPos As Long)
If InStr(Trim(varTemp), " ") = 0 Then Exit Function
lngPos = lngPos - 1
arrTemp = Split(Trim(varTemp), " ")
If lngPos UBound(arrTemp) Or lngPos < 0 Then Exit Function
GetWord = arrTemp(lngPos)
End Function

If this post helps click Yes
---------------
Jacob Skaria


"Harlan Grove" wrote:

Jacob Skaria wrote...
....
Function SecondorThirdWord(varTemp)
If InStr(varTemp, " ") = 0 Then Exit Function
arrTemp = Split(varTemp, " ")
If UBound(arrTemp) 1 Then
SecondorThirdWord = arrTemp(2)
Else
SecondorThirdWord = arrTemp(1)
End If
End Function
....

What a nearly useless udf!

All it takes to make this general is adding a second argument to the
UDF to allow the user to specify which word to return.


Function w(s As String, k As Long) As Variant
Dim v As Variant

k = k - 1 'Split returns 0-based arrays, but this udf uses 1-based
v = Split(Application.WorksheetFunction.Trim(s), " ")

If LBound(v) <= k And k <= UBound(v) _
Then w = v(k) _
Else w = CVErr(xlErrValue)

Erase v
End Function

On Sat, 2 May 2009 21:02:01 -0700, Jacob Skaria
wrote:

Just one thing TRIM() function is a VBScript function which is also a
worksheet function. So you can directly call TRIM..

If you examine these functions, you will see that they are NOT the same. In
particular, and most likely why Harlan used the worksheet function and NOT the
VBA function, is the difference in how spaces within the string are treated.
--ron