Simple Separation of Bibliographic Fields in Excel

Question

I have a simple problem which I am unable to solve (I'm no good at VBA). I'm trying to separate data which looks like this:

[Buckley, Peter J.] Univ Leeds, Sch Business, Ctr Int Business, Leeds LS2 9JT, W Yorkshire, England; [Hashai, Niron] Hebrew Univ Jerusalem, Jerusalem Sch Business Adm, IL-91905 Jerusalem, Israel

Basically - author inside square brackets (could be more than one author), then his/their affiliation, then the second author inside square brackets and his affiliation, and so on (in some cases there are 10-15 authors). I wish to separate each line so that I have each other in one column and his affiliation in the column next to it. If two authors have the same affiliation, they should be in different rows, with their affiliation written next to each one of them.

I would really appreciate your help!

yes - there are a few thousand cells similar to the one posted above — user3139820, Mar 30 '14 at 06:32

score 0 · Answer 1 · edited May 23 '17 at 11:49

You might consider using Regex for pattern matching. For example, if you have an entry in cell A1 like this:

[Buckley, Peter J.] Univ Leeds, Sch Business, Ctr Int Business, Leeds LS2 9JT, W Yorkshire, England;

Add a reference to your VBA project to: "Microsoft VBScript Regular Expressions 5.5"

See this post for more details.

This code will extract the author and display it in a message box:

Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1")

    For Each C In Myrange
        strPattern = "(^\[)([a-zA-Z]*\,\s[a-zA-Z]*\s[a-zA-Z]\.?)(\])(.*)"

        If strPattern <> "" Then
            strInput = C.Value
            strReplace = "$1"

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.test(strInput) Then
                MsgBox ("Author: " & regEx.Replace(strInput, "$2"))
            Else
                MsgBox ("Not Matched")
            End If
        End If
    Next
End Sub

Results:

enter image description here

Simple Separation of Bibliographic Fields in Excel

1 Answers1