String variable correction

Hello,
I have string variable with multiple spaces and extra lines on the end, example:
"
Investor: 1000025535 Ontario Inc., New York, NY, USA (USA)

    Canadian Business: 
    
        
                Replica Analytics Ltd., Ottawa, ON, CAN
                Business Activity: Develops synthetic data software.

"

I want to have:
"
Investor: 1000025535 Ontario Inc., New York, NY, USA (USA)
Canadian Business:
Replica Analytics Ltd., Ottawa, ON, CAN
Business Activity: Develops synthetic data software.
"
Later on, I want to put this in two Excel columns.
Any help would be appreciated.
Thank you,
Brano

What added to complexity, it can be more than one “Canadian business”:

First Company, Toronto, ON, CAN
Business Activity: Provides energy and sustainability management software and services for commercial real estate.

                Second company, Vancouver, BC, CAN
                Business Activity: Provides energy and sustainability management software and services for commercial real estate.

Hello @Vuk

I think @Yoichi already provided a soljtion in another post. Did you tried that???

Hello Rahul,
Unfortunately, it didn’t work as expected, so I used the first solution which gave this result. Any ideas on this text file?
Thanks,
Brano

Hello, I would suggest a few things. Please let me know if it works.

  1. I would begin by splitting your string variable by Environment.NewLine. The result is a string array. — Assign stringArr var = String.Split(inputVar,Environment.NewLine)
  2. I would go through each line of your string array using a For Each activity and use the Trim activity to get rid of spaces.
  3. To remove the extra spaces between lines (e.g. after ‘Canadian Business’), use a For Each activity to iterate each line and an If activity to check if the line is blank. There is a method - String.isNullorWhitespace which will check if the line is blank.
  4. Which lines do you want to place in which column of Excel? Based on this, you can create an empty datatable and then add the corresponding row the datatable. Then, export it to Excel.

Thank you Aman,
Will try, sounds promising.
Kind regards,
Brano