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.