Turns out there IS a simple solution that Google has that Excel does not - and this is the =SPLIT function. Who would have thought.
Two ways you can do it - give you an output like this:
Depending on what you want.
I’ve made a Google Template with an example in.
But in essence the SPLIT function works like this:
=SPLIT([data to split],["delimiters to use"],[split by each character or not],[remove empty text or not]
In our case it looks something like this:
: and , are both treated as unique delimiters - so it will find each iteration. What I’m not sure about is how numbers are formatted in the Category Split column - if anything > £1,000 has a , it will break it.
So that formula (or a variation) goes into the yellow cell, and will fill as far as it needs to the right until all delimited chars are used up.
Because of that behaviour, there needs to be space enough for all these columns to auto fill - so don’t add anything to the right of this formula (or leave enough columns blank).
If you want to use this so all categories are here - then you need to add some IF statements to pick up the non-split categories. (Top example in the link)
If you want to use this to ALSO capture transaction values that cannot be categorised - then you need to add a 2nd if - in the 2nd example.
These IF’s are a bit Janky though - as they effectively “force” 2 sets of data to be read as a single string.
have a look and see what you think