CODEWITHSUNDEEP
arraysvbaexcel
crashwap
crashwap

Reputation: 3062

Get nth element from VBA string array

How would I extract the nth element from a VBA string array?

I have a string, like: TEST - 2017/01/20 = Invoice

I wish to extract out the 2nd and 3rd elements as concisely as possible. I was trying something Javascripty like this, which doesn't work, so there must be a VBA way:

Dim Report As String
Dim CurrLine As String
Dim CurrDate As String
Dim CurrTask As String

CurrLine = "TEST - 2017/01/20 = Invoice"
CurrDate = Trim(Split(CurrLine, '-')[1])
CurrTask = Trim(Split(CurrLine, '=')[1])
Report = Report & CHR(34) & CurrDate & CHR(34) & "," & CHR(34) & CurrTask & CHR(34)

//result: "2017/01/20","Invoice"

Upvotes: 4

Views: 5385

Answers (2)

Vityata
Vityata

Reputation: 43585

I would have used the solution of @h2so4 with replacement (especially if it is a job related task), but if you are a fan of "fancy" coding, this works as well:

Option Explicit

Public Sub Test()

    Dim strtest As String

    strtest = "Test - 2017 / 1 / 20 = Invoice"

    Debug.Print Trim(Split(strtest, "-")(1))
    Debug.Print Trim(Split(strtest, "=")(1))
    Debug.Print Trim(Split(Trim(Split(strtest, "-")(1)), "=")(0))

End Sub

Upvotes: 3

h2so4
h2so4

Reputation: 1577

a possible solution

Dim Report As String
Dim CurrLine As String
Dim CurrDate As String
Dim CurrTask As String, St 


CurrLine = "TEST - 2017/01/20 = Invoice"
St=Split(replace(CurrLine, "=","-"),"-")
CurrDate = St(1)
CurrTask = St(2)
Report = Report & CHR(34) & CurrDate & CHR(34) & "," & CHR(34) & CurrTask & CHR(34)  //result: "2017/01/20","Invoice"

Upvotes: 5

Related Questions