PDA

View Full Version : Excel formula to extract string



jadgon
07-06-2017, 03:59 AM
Extract all sentences in a cell containing the word "shall" and "body" if both exists.
Extract all sentences in a cell containing the word "She", "shall" and "body".
Extract all sentences in a cell containing the word "She", "shall", "go" and "body".

mdmackillop
07-06-2017, 04:11 AM
FAQ (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_posthelp_faq_item)

SamT
07-06-2017, 04:17 AM
Are you asking about ...
One cell with many sentences?
Many Cells with many sentences?
Many Cells with one sentence?

Are you asking for the exact words: "She", "shall", "go" and "body", or are they just to represent "string1," "string2," "string3," and "string4?"

Does the solution have to be a formula using only Excel Functions?
OR
Can it be a User Defined VBA Function that you can use in a formula?

Please answer all three multiple part questions

jadgon
07-06-2017, 04:51 AM
1. Many Cells with many sentences?

2. Are you asking for the exact words: "She", "shall", "go" and "body", or are they just to represent "string1," "string2," "string3," and "string4?"
Answer - Both if possible. But exact is preferred first.

3. VBA is preferred. But Excel formula is also file.

jadgon
07-07-2017, 05:46 PM
Extract all sentences in a cell containing the word "shall" and "body" if both exists to next cell.

Paul_Hossler
07-07-2017, 06:43 PM
Details?

Example?

Attachment?

Sample?

SamT
07-07-2017, 07:01 PM
Please do not start new threads with the same problem.

Moderator,
SamT

mikerickson
07-07-2017, 10:07 PM
Perhaps this will help you.

Sub test()
Dim strTest As String
strTest = "This is a long sentences. This isn't. This is a very long sentence. This is short?"
MsgBox ExtractSentences(strTest, "long sentence")
End Sub

Function ExtractSentences(ByVal aString As String, ContainingWords As String, _
Optional SentenceEnders As String = ".?!", Optional Punctuation As String = ",-;:") As String

Dim Sentences As Variant, Words As Variant
Dim TestFor As Variant, flag As Boolean

Dim i As Long, j As Long
For i = 1 To Len(SentenceEnders)
aString = Replace(aString, Mid(SentenceEnders, i, 1), ".")
Next i
aString = Replace(aString, vbCr, " ")
aString = Replace(aString, vbLf, " ")
aString = WorksheetFunction.Trim(aString) & " "
aString = LCase(aString)
Sentences = Split(aString, ". ")

TestFor = Split(LCase(ContainingWords), " ")
For i = 0 To UBound(Sentences)
Sentences(i) = " " & Sentences(i) & " "
flag = True
For j = 0 To UBound(TestFor)
If InStr(1, Sentences(i), " " & TestFor(j) & " ") = 0 Then
flag = False
Exit For
End If
Next j
If flag Then
ExtractSentences = ExtractSentences & "," & Trim(Sentences(i))
End If
Next i
ExtractSentences = Mid(ExtractSentences, 2)
End Function

jadgon
07-14-2017, 07:10 PM
My multipara data is in multiple cell in column A.

SamT
07-15-2017, 07:24 AM
B1 Formula = "=ExtractSentences(A1, "long sentence")"