Advent of Code 2015 - Day 12: JSAbacusFramework.io

Part 1

Description

Santa's Accounting-Elves need help balancing the books after a recent order. Unfortunately, their accounting software uses a peculiar storage format. That's where you come in.
They have a JSON document which contains a variety of things: arrays ([1,2,3]), objects ({"a":1, "b":2}), numbers, and strings. Your first job is to simply find all of the numbers throughout the document and add them together.
For example:

• [1,2,3] and {"a":2,"b":4} both have a sum of 6.
• [[[3]]] and {"a":{"b":4},"c":-1} both have a sum of 3.
• {"a":[-1,1]} and [-1,{"a":1}] both have a sum of 0.
• [] and {} both have a sum of 0.

You will not encounter any strings containing numbers.
What is the sum of all numbers in the document?

Input

Solution

I succeeded solving this problem without using VBA. First, I split the input into values separated by commas. This can be done by pasting the input in Notepad and save it as a CSV (Comma Separated Values) file and open it in Excel. It will look like this:

The input is split into cells in rows 4. In row 5, I put this formula:

=IFERROR(VALUE(LEFT(RIGHT(B4,LEN(B4)-MIN(FIND({"-",0,1,2,3,4,5,6,7,8,9},B4&
"-0123456789"))+1),SUM(LEN(RIGHT(B4,LEN(B4)-MIN(FIND({"-",0,1,2,3,4,5,6,7,8,9},B4&
"-0123456789"))+1))-LEN(SUBSTITUTE(RIGHT(B4,LEN(B4)-MIN(FIND({"-",0,1,2,3,4,5,6,7,8,9},
B4&"-0123456789"))+1),{"-","0","1","2","3","4","5","6","7","8","9"},""))))),0)

What does this formula do? First, it separate the number (including negative ones, note the "-" sign) and non-number character after that from the string. For example, a string "a:[2]" becomes "2]". Then, it separates the number for any non-number character. VALUE() is needed to convert those number string cells into their values. While IFERROR() is needed to avoid error of conversion of empty strings.

Part 2

Description

Uh oh - the Accounting-Elves have realized that they double-counted everything red.
Ignore any object (and all of its children) which has any property with the value "red". Do this only for objects ({...}), not arrays ([...]).

• [1,2,3] still has a sum of 6.
• [1,{"c":"red","b":2},3] now has a sum of 4, because the middle object is ignored.
• {"d":"red","e":[1,2,3,4],"f":5} now has a sum of 0, because the entire structure is ignored.
• [1,"red",5] has a sum of 6, because "red" in an array has no effect.

Solution

For the part 2, I have to use JSON editor to remove all objects containing "red". After that it's just the same with the part 1.