Extract all instances of a search between two symbols

5 pts.
Tags:
Excel VBA
Microsoft Excel
VBA
Dear Friends, I have created the following code to search entire C Column from WrkBk1 with "<*>" string so as to extract all the matched words into Wrkbk2. for eg a single cell data in Col-C is given below maintaining similar pattern in other cells too. [Cell C1 Data] Enter Pers No. <pernr> Verify: 1. Effective Date <effdate> 2. Action Type <actiontype> 3. Action Reason <actreason> 4. Organisational Assignment <orgassignment> 5. Last Working Day <lastwrkgday> 6. Eligible for Rehire <Eligrehire> Now my code below is pulling full para between first and last "<" ">" instead of all the words in <>. Here it is below: Sub GetFullName() Dim str1 As String, str2 As String, rng As Range, beginPos As Integer, endPos As Integer str1 = InputBox("String start?") str2 = InputBox("String end?") For Each rng In Range("A1", Range("A1").End(xlDown)) beginPos = InStr(1, rng.Value, str1) On Error Resume Next endPos = InStr(beginPos, rng.Value, str2) On Error GoTo 0 If endPos = 0 Then endPos = Len(rng.Value) + 1 If InStr(1, rng.Value, str1) > 0 Then rng.Offset(0, 1) = Mid(rng.Value, beginPos, endPos) End If Next rng End Sub My actual out put should be split and extracted to new Wrkbk2 with 3rd adjacent cel being name of the source workbook: For Eg: A B C Pers No. <pernr> WrkBk1 Effective Date <effdate> WrkBk1 Action Type <actiontype> WrkBk1 Action Reason <actreason> WrkBk1 Any VB/Macro experts please suggest some code change so that it works as desired. Quick responses are highly appreciated.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following