CODEWITHSUNDEEP
excelvba
Jordan
Jordan

Reputation: 363

Using Split To Separate only 1st instance-VBA

I have a situation where I need to separate a statement pulled from SAP based on the word "Caller" The issue is that this word is used multiple times throughout the statement but I need it to only cut off the 1st sentence leading up to the 1st "Caller". Is there a way to have split separate these words out and then recombine everything split except the (0) instance.

Here is my code that I am using right now.

Description_Rough = session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB01/ssubSUB_GROUP_10:SAPLIQS0:7235/subCUSTOM_SCREEN:SAPLIQS0:7212/subSUBSCREEN_4:SAPLIQS0:7715/cntlTEXT/shellcont/shell").Text

Descrption_Final = Split(Description_Rough, "Caller")(1)

And the statement being pulled as description_rough is

08.08.2018 21:53:55 UTC "Name" (Numbers) Phone #########
17.07.2018 16:25:47 AAAAAA AAAAAA (AAAAAA)
Caller is patient using device. Caller reports that she has been using
current device for about 9 days and that the problems arose with the device. Caller reports that she monitors the device
each dose and does not recall what dose was in the device before it went to all red.  Patient to return affected device to
Company; mail order pharmacy to replace to patient; Company to replace to
Pharmacy.

What I am looking for is everything beginning at "Caller is patient using device."

Upvotes: 1

Views: 74

Answers (2)

Marcucciboy2
Marcucciboy2

Reputation: 3258

This should work. It just starts from the second occurrence of "Caller" and pieces it all back together from there.

Description_Rough = "Caller is patient using device. Caller reports that..."

Dim temp As Variant
temp = Split(Description_Rough, "Caller")

Dim Description_Final As String

Dim i As Long
For i = LBound(temp) + 1 To UBound(temp)
    Description_Final = Description_Final & "Caller" & temp(i)
Next i

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149315

Simplest way to do it in my opinion

Sub Sample()
    Dim Descrption_Final As String
    Dim pos As Long

    Descrption_Final = "17.07.2018 16:25:47 AAAAAA AAAAAA (AAAAAA) Caller is patient Blah Blah....."

    pos = InStr(1, Descrption_Final, "caller", vbTextCompare)

    If pos > 0 Then Debug.Print Mid(Descrption_Final, pos)
    '~~> Output: Caller is patient Blah Blah.....
End Sub

And if you want everything before Caller is patient Blah Blah..... then use 0 instead of 1

Split(Description_Rough, "Caller")(0)

Example

Sub Sample()
    Dim Descrption_Final As String
    Dim pos As Long

    Descrption_Final = "17.07.2018 16:25:47 AAAAAA AAAAAA (AAAAAA) Caller is patient Blah Blah....."

    Debug.Print Split(Descrption_Final, "Caller")(0)
    '~~> Output: 17.07.2018 16:25:47 AAAAAA AAAAAA (AAAAAA)
End Sub

Upvotes: 4

Related Questions