How to split array in excel vba

December 13, 2022

I had a value in Cells(1,1) as below:

**[[{"Name":Ashwin ,"Age":64}],[],[{"Name":Shakur ,"Age":64,"Gender":Male}]]**

Since it has various i need to print last array element.
My expected output as below
{"Name":Shakur ,"Age":64,"Gender":Male}

Here my code :

set ws=worksheets("sheet1")
jsontext=worksheets("sheet1").cells(1,1)
set Jsonobject=JsonConverter.ParseJson(jsontext)

for each Item in Jsonobject
 if Item="Gender" then 
 end if
next

Cells(1,1) has the above [[{"Name":Ashwin ,"Age":64}],[],[{"Name":Shakur ,"Age":64,"Gender":Male}]]

When am running the above loop,it was failed due to "[[" in starting.I removed it in cell and run it ,it will considered only {"Name":Ashwin ,"Age":64}.I need to loop until 3rd array…So i tried to split up into array wise and tried to run it in code

>Solution :

Option Explicit
Sub ExampleSplit()
Dim s As String, vx() As String
s = "**[[{""Name"":Ashwin ,""Age"":64}],[],[{""Name"":Shakur ,""Age"":64,""Gender"":Male}]]**"
vx = Split(s, "{")
MsgBox "{" & Split(vx(UBound(vx)), "}")(0) & "}"
End Sub