I’ve retrospectively split some transactions into two different categories. This is not shown on my Google Sheets export. Is this because I’ve retrospectively changed them, or is it because the sheets export only supports one category per transaction? If it’s the latter, it feels like a significant limitation to accuracy of the Sheets data to me.
Very sorry if this is answered elsewhere, I had a search and couldn’t find anything.
There’s a post somewhere on here from Monzo explaining that there were some issues with having split transactions display as two lines.
You should however see the category split in column P.
Ah yeah, I see. I need a wider screen, haha!
Hopefully they can resolve it as I’m not sure my spreadsheet skills are good enough to extract and split the data from a single cell like that.
Can you do a screenshot or snip the part? I don’t have anything in two categories to test.
Then I (or someone smarter) can help you split it apart with some formula.
This is how it appears in the cell. The column (column P) is headed “Category Split”
Booze:-40.00,Groceries:-71.56
It sounds like this is probably a temporary workaround, but it would be easier to manage if they split that out into four columns.
I can give you the formulas for each cell but it’s a bit long and you’ll need the extra columns.
The easier option will be to wait for @BritishLibrary to see this and he’ll probably do it a bit cleaner than me!
Thank you. I might have a stab at totally bodging it myself later and then come back here see how others have done it better. It’s a good way to learn 
That’s a neat solution 
Have you got a nice way to account for that in pivots / tables yet?
That’s my biggest problem with having to split the data ‘ourselves’ - having to have weird workarounds to present the data.
I’m not sure how you’d sum it in the end either.
When you split it, what is shown in column G?
I just typed up a response but it didn’t show. I think I somehow replied to a new thread?
First off I thought this was an older thread - so sorry I didn’t spot this sooner.
In terms of solutions - I think the best will be script / macro to turn that into 2 new lines. But that is a pain and not straight forward at all. I’m going to have a crack at writing one this weekend though, so watch this space.
What I think @revels has done here (search/find functions?) is probably the way I’d approach it - you might be able to reduce columns by a little but you’re always going to need columns this way I think - (which I why I’m not a fan of the data being structured this way!)
Will have a think this aft and see if I can find a way / how close it is to Revels!
I don’t think it helps that there’s about 8 threads with us being excel nerds!
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:
=SPLIT([Column P],":,",True,True)
: 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
Final thing - the formula you want would be:
=if(P9="",if(G9="",split(":"&H9,":,"), split(G9&":"&H9,":,")), split(P9,":,",true,true))
(Column refs in relation to the original data set)
There is a “zero width space” character infront of the first : Hopefully this allows for summing and pivoting without breaking stuff - so copy directly
That’s working a treat - thank you so much. Haven’t delved into using it in detail yet, but have tested it against a bunch of split transactions and it seems to be working perfectly.
I followed and understood most of that too, and I’m no Sheets wizard. I am quite familiar with IF functions, so that helped with part of it 
This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.