CODEWITHSUNDEEP
vbams-word
Learning
Learning

Reputation: 868

Split the string till specific substring

I have a reference list. I have to compare author's name with citation authors in the reference list. I have to split the string till the year, so I can get only author names, but I don't have an idea how to proceed further.

For example, given the following text:

Sperrin, M., Jaki, T., & Wit, E. (2010). Probabilistic relabelling strategies for the label switching problem in Bayesian mixture models. Statistics in Computing, 20, 357-366.

I have to extract the author name:

Dim txt As String
Dim txtarray() As String
txt = "Sperrin, M., Jaki, T., & Wit, E. (2010). Probabilistic relabelling strategies for the label switching problem in Bayesian mixture models. Statistics in Computing, 20, 357-366."
txtarray = Split(txt, ",")

//Split will split the entire text, and I need the text only until the year

Expected output: txtarray = ("Sperrin", " M.", " Jaki", " T."," & Wit")

Upvotes: 0

Views: 118

Answers (1)

Alex P
Alex P

Reputation: 12489

Try this:

Sub GetAuthors()
    Dim txt As String, authors As String, authorsArray() As String

    txt = "Sperrin, M., Jaki, T., & Wit, E. (2010). Probabilistic relabelling strategies for the label switching problem in Bayesian mixture models. Statistics in Computing, 20, 357-366."
    authors = VBA.Trim(VBA.Mid$(txt, 1, InStr(1, txt, "(", vbTextCompare) - 1))
    authorsArray = VBA.Split(authors, ",")
End Sub

Basically this searches for the first ( to find where the date starts, creates a substring of the authors only, and the splits it on ,.

Clearly this only works if the date is present and of the format (YYYY)

Upvotes: 1

Related Questions