Trailing Whitespace Text File.

[Inactive User]

Hi All, can someone help me to delimit the data below. 

For the amounts it should have 3 columns

debit, credit, balance

I tried to used the replace the whitespaces using regex and replace it with pipe(|). However, the extra spaces are delimited. The extra spaces should be retain and have 0 as value.

[jennifer_owens · Accepted solution]

There might be a better way to do this in RegEx but I think your data has predefined whitespace for each cell (debit is X characters wide with all whitespace if null and some data plus white space if not). I did my best with your data, but it might need to be reworked with more data. 

I just cut your data apart based on what I could see using Left and Right trim and some cleaning. I hope it gives you a start! 

(This is my first time answering a community post but if this does the trick, please mark it as solved).