Isolate data inside [ ] ; and ,

[elbrujo]

Hi there,

I have a format like this:

ID	Author Address
1	[Amaya-Amaya, J.; Calvo-Paramo, E.; Calderon-Paez, A.; Torralvo-Morato, G.; Calixto, O. J.; Mantilla, R. D.; Anaya, J. M.; Rojas-Villarraga, A.] Univ Rosario, Sch Med & Hlth Sci, Ctr Autoimmune Dis Res CREA, Bogota, Colombia.

I need to Isolate the data into columns, but giving priority to the first 'sentence' (everything inside the commas ,) after the ], the authors (the names inside [ ] ), and the countries (the very last word)

In order to get this:

1	Amaya-Amaya, J.	Univ Rosario	Colombia.
1	Calvo-Paramo, E.	Univ Rosario	Colombia.
1	Calderon-Paez, A.	Univ Rosario	Colombia.
1	Torralvo-Morato, G.	Univ Rosario	Colombia.
1	Mantilla, R. D.	Univ Rosario	Colombia.
1	Anaya, J. M.	Univ Rosario	Colombia.
1	Rojas-Villarraga, A.	Univ Rosario	Colombia.

How can I achieve this? I really need this with urgency, but have no idea on how to do it

Certainly I don't care if the other columns remain in the result sheet, as long as I can isolate the information for those three, I can rearrange the format later on...

There are some rows where there's more than "one country", which would look like:
[Name; Name; Name;] Data, Data, Data, Country. [Name; Name; Name;] Data, Data, Country.

But it is still the very last word that I need as the country...

Attached is an example Book

[GC Excel]

Hello,

This is similar to one of your previous post...
Try this code :

'This macro to parse the data
'2015-05-13
Sub EF1082541()
    Dim ar, arS, arS2, arS3, arGroup
    Dim i As Long, ii As Long, iii As Long, n As Long, k As Long
    Dim sStr As String, sGroup As String, sCountry As String
    Dim sAuthors, sAddress As String
    Dim arTemp

    ar = Sheets(1).Cells(1).CurrentRegion.Value

    With Sheets(2)
        n = 1

        'Loop through the data
        For i = 1 To UBound(ar, 1)

            'Skip empty rows
            If ar(i, 2) <> "" Then

                'Get the full string
                sStr = ar(i, 2)

                'Get the country (last value in the string)
                arTemp = Split(sStr, ",")
                sCountry = arTemp(UBound(arTemp))
                
                'Remove the email from the country
                sCountry = Split(sCountry, ";")(0)
                
                'For USA, remove the state and remove the dot
                arTemp = Split(sCountry, " ")
                sCountry = Replace(arTemp(UBound(arTemp)), ".", "")

                'Split in case we have multiple group of authors " ["
                arGroup = Split(sStr, " [")

                'Loop within the group of authors
                For k = LBound(arGroup) To UBound(arGroup)
                    sGroup = arGroup(k)

                    'Separate the group of authors and the address
                    If InStr(1, sGroup, "] ") > 0 Then
                        sAuthors = Split(Mid(sGroup, 2), "] ")(0)
                        sAddress = Split(Split(sGroup, "] ")(1), ",")(0)
                    Else
                        sAuthors = ""
                        sAddress = Split(sGroup, ",")(0)
                    End If

                    'Split the authors by ;
                    If InStr(1, sAuthors, "; ") > 1 Then
                        sAuthors = Split(sAuthors, "; ")
                    End If

                    'Output results
                    If IsArray(sAuthors) Then
                        For ii = LBound(sAuthors) To UBound(sAuthors)
                            .Cells(n, 1) = ar(i, 1)
                            .Cells(n, 2) = sAuthors(ii)
                            .Cells(n, 3) = sAddress
                            .Cells(n, 4) = sCountry
                            n = n + 1
                        Next ii
                    Else
                        .Cells(n, 1) = ar(i, 1)
                        .Cells(n, 2) = sAuthors
                        .Cells(n, 3) = sAddress
                        .Cells(n, 4) = sCountry
                        n = n + 1

                    End If
                Next k
            End If
        Next i

        'Autofit columns
        .Cells(1).CurrentRegion.Columns.AutoFit

    End With

End Sub

[elbrujo]

It is, indeed, and I used that macro, unfortunately found several discrepancies... And preferred to open a new thread, but didn't imagine you would help me once again!,

I deeply appreciate your help, thank you very much @GC Excel

It worked perfectly!

[GC Excel]

You are welcome...
Thanks for the rep.