Text to Columns

[Rayyyyy]

Hi Everyone, 

I would like to ask for you help how I can transform this unformatted text from the PDF file into a 6 columns format below.  Thanks in advance!

[caltang]

Can you provide the data please?

[Rayyyyy]

Hi @caltang 

Please refer to the attached file. Thanks in advance

[caltang]

Okay, I transcribed the data for you. 

To answer your query, you can use REGEX as I see your pattern is quite standard.

For the DR CR formatting, I will leave that to you to decide on what you want.

[caltang]

For anyone curious, the REGEX is:

^(.*?) (.*?) (.*?) (.*?) (.*?) (.*?)$

[caltang]

Sorry, just noticed that your data exists in rows of 2s. PFA latest.

[caltang]

I think your sample data for Record #3 is missing its partner row lol.... nevermind, I've transcribed the data for you and added the Debit and Credit as well as a final touch. Hopefully this gives you an idea!

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

Best regards,
Calvin Tang
https://www.linkedin.com/in/calvintangkw/

[Rayyyyy]

Hi @caltang 

Thanks for the solution it helped a lot. however I have one more concern,

there are transactions that have more than 2 lines. I need the two rows only and I don't need the rest of the lines and get to the next transaction. Please help. Thanks in advance! Please see attached file. 

[caltang]

Hey sure, I’m outside at the moment. I’ll look at it when I have time.

[caltang · Accepted solution]

@Rayyyyy sorry for the delay, PFA.