How to split array in excel vba

Question

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

SO is not a code-writing service - please post what you have tried so far, and we can help you improve on it. — Spencer Barnes
– Spencer Barnes, Commented Dec 13, 2022 at 8:12
You tell us what you want to achieve but neither what you tried, what difficulties you encountered nor are you asking a question. — FunThomas
– FunThomas, Commented Dec 13, 2022 at 8:15

user19618751 · Accepted Answer · 2022-12-13 08:24:06Z

1

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

answered Dec 13, 2022 at 8:24

user19618751

Sign up to request clarification or add additional context in comments.

2 Comments

Yes_par_row Over a year ago

Thanks.It worked as expected......I need to display only the gender value in Dictionary means how we can specify.Now i need to display the value "male" only

user19618751 Over a year ago

This should be a new question? But you could try first on similar approach by yourself

Collectives™ on Stack Overflow

How to split array in excel vba

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related