Ideas welcome - formatting trial balance

[smurad008]

I am generating a trial balance that is ultimately going to look like this : 

My current trial balance looks like this.

1. I can't seem to figure it out with data cleansing but I need to remove leading spaces so I can separate the number from the Name. 
2. Second I need to somehow eliminate the total column so I can only get the individual account that makes up the total. 
3. When I tried [TB Accounts] = "checking:" or [TB Accounts]="Account Name" - I thought it was too much, maybe a faster way to do it? If the total name changes?

Can any of you help me with this? I attached a sample file. I appreciate any help. 

[fmvizcaino · Accepted solution]

Hi @smurad008 ,

Attached is an example showing how to do it.

Let me know if you need further assistance.

Best,

Fernando Vizcaino

[smurad008]

Thank you so much!

[smurad008]

I expanded my data set to the following attached: I added the names to the TRIM but was still running into errors. Can you help me on what I would do to eliminate in the output? 

I appreciate your support! I am not sure what I am missing 😞 

[fmvizcaino]

Hi @smurad008 ,

I've added one more condition in the filter, to remove anything with 'Net' and 'Gross' also.

Take a look at the result.

Best,

Fernando Vizcaino