return second or third word in a cell
 

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.

return second or third word in a cell
 

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),"")

return second or third word in a cell
 

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

return second or third word in a cell
 

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.

return second or third word in a cell
 

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

return second or third word in a cell
 

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

return second or third word in a cell
 

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

return second or third word in a cell
 

Dear Ron

Just curious, is there a funtionality difference?

What I understood was that both products (Excel and VBA) had the TRIM()
functionality before even Microsoft integrated both products in XL5

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


"Ron Rosenfeld" wrote:

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

return second or third word in a cell
 

On Sun, 3 May 2009 10:38:02 -0700, Jacob Skaria
wrote:

Dear Ron

Just curious, is there a funtionality difference?


Why do you think I wrote what I did when I wrote "... the difference in how
spaces within the string are treated." Did that not make it clear to you that
there was a "funtionality difference"?

What I understood was that both products (Excel and VBA) had the TRIM()
functionality before even Microsoft integrated both products in XL5

The worksheet function and the VBA function are, and were, different.

--ron

return second or third word in a cell
 

msgbox "***" & trim(" qwer qwer ") & "***"
msgbox "***" & application.trim(" qwer qwer ") & "***"


Jacob Skaria wrote:

Dear Ron

Just curious, is there a funtionality difference?

What I understood was that both products (Excel and VBA) had the TRIM()
functionality before even Microsoft integrated both products in XL5

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

"Ron Rosenfeld" wrote:

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


--

Dave Peterson

return second or third word in a cell
 

Thanks Ron & Dave...have never noticed this difference... may be because I am
more addicted to VB & VBScript...

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


"Dave Peterson" wrote:

msgbox "***" & trim(" qwer qwer ") & "***"
msgbox "***" & application.trim(" qwer qwer ") & "***"


Jacob Skaria wrote:

Dear Ron

Just curious, is there a funtionality difference?

What I understood was that both products (Excel and VBA) had the TRIM()
functionality before even Microsoft integrated both products in XL5

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

"Ron Rosenfeld" wrote:

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


--

Dave Peterson