excel - How do I grab the first element from this string that looks like an array? -


in excel, have cell containing string looks this:

[[1.234,3.21],[2.345,3.21]]

i want average of 1.234 , 2.345 in 1 cell, , average of 3.21 , 3.21 in other.

i've never worked string looks array other programming languages before.

how can achieve want excel formula?

you use following:

=average(--trim(mid(substitute(substitute(substitute($a1,"[",""),"]",""),",",rept(" ",99)),((column(a:a)-1)*99)+1,99)),--trim(mid(substitute(substitute(substitute($a1,"[",""),"]",""),",",rept(" ",99)),((column(b:b))*99),99))) 

then drag across 1 , down far needed

enter image description here


Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -