return second or third word in a cell

  • Thread starter Thread starter birdgirl31
  • Start date Start date
B

birdgirl31

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 said:
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.
Click to expand...

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(TRIM(x)&" "," ",CHAR
(127),k))-1),
1,IF(k>1,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
 
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
 
Jacob Skaria said:
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
Click to expand...
....

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
 
Just one thing TRIM() function is a VBScript function which is also a
worksheet function. So you can directly call TRIM..
Click to expand...

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
 
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
 
Dear Ron

Just curious, is there a funtionality difference?
Click to expand...

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
Click to expand...

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

--ron
 
msgbox "***" & trim(" qwer qwer ") & "***"
msgbox "***" & application.trim(" qwer qwer ") & "***"
 
Thanks Ron & Dave...have never noticed this difference... may be because I am
more addicted to VB & VBScript...

If this post helps click Yes
 
Back
Top