Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Find Word from long statement

  Asked By: Oscar    Date: Oct 20    Category: MS Office    Views: 798
  

I have written a long statement in a cell, and i want
to find particular word from it, is there any formula
available to do so.

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Al Allen     Answered On: Oct 20

I presume you mean a statement  in a specific cell. If so, you can use
the Excel find  function to find it.

If you want to find the position in the statement where the word  is
located, there are functions available for that as well.

Please specify your exact issue with more detail.

 
Answer #2    Answered By: Viola Hanson     Answered On: Oct 20

I have written  in one cell  the following statement:
"THAT THE QUICK BROWN FOX JUMPS RIGHT OVER THE LAZY
DOG".
and I want to extract word  "FOX". That is the problem.

 
Answer #3    Answered By: Arthur Cole     Answered On: Oct 20

I have written  a small program to your problem have a look at my code
below

Sub om()
Dim a As String
Dim temp As String

a = Range("a1")
strlen1 = Len(a)
i = 1
While (strlen1 <> 0)
temp = Mid(a, i, 3)
If (temp = "FOX") Then
gotit = "Yes"
pos = i
End If
i = i + 1
strlen1 = strlen1 - 1
Wend
If (gotit = "Yes") Then
MsgBox ("Fox does exist at " & pos & " character to " & pos + 3)
Else
MsgBox ("Fox does not exist")
End If
End Sub

I hope this will be useful to you.

 
Answer #4    Answered By: Jim Williamson     Answered On: Oct 20

I'd like to add on to what Sujay has contributed, which is to introduce the
VBA InStr() function to those who might not be familiar with it. It could be
used in Sujay's code like this:



Sub om()

Dim a As String

a = Range("a1")

pos = InStr(s, "FOX", vbCompareText)

If pos > 0 Then

MsgBox ("Fox does exist at " & pos & " character to " & pos + 3) Else

MsgBox ("Fox does not exist")

End If

End Sub



InStrRev() finds strings starting at the end of the string instead of the
beginning, which is sometimes useful also.

 
Answer #5    Answered By: Sherri Parker     Answered On: Oct 20

That doesn't really make sense. Why extract a word  you already know?

Do you mean that you want to check whether it has the word FOX in it?

Or do you want to find  the position of the word FOX in it?

Or do you want to extract the three characters at this position in the
string?

Or do you want to extract the fifth word from the string?

 
Didn't find what you were looking for? Find more on Find Word from long statement Or get search suggestion and latest updates.




Tagged: