Split a text into 50-character long strings and a break line

Dear All,

I have description text that comes in a field. I need to split the text into lines. Each line cannot be more than 50 character long. And each line must finish with a breakline character.

the difficulty is that a word cannot be broken, i.e if the 50th character is in the middle of a word, then the line should be broken before.

and last but not least, the description should remain in one single field.

I have attached an example. In column A the text comes with no beak lines. In column 2 there is a beakline after the word that is closest to the 50 character.

I hope I am clear enough.

thank you for you help

myriam

Length of 50 char.xlsx

Tips and Tricks

Preparation

Accepted answers

ponraj

Here is my solution for your case. Steps for formatting excel output as below. Hope this is helpful.

1.Open the excel output

2.Press Ctrl+A to select the output text.

3.Click Home > Format > AutoFit Column Width

4.Click Home>Wrap Text to get the desired output.

Workflow Result ResultExcel output

Split a text into 50-character long strings and a break line.yxmd

msantoso

Hi Ponraj,

thank you so much for the solution!!

One more thing I learned today

and one topic that I can close for today!

Have a nice day

Myriam

All comments

BenMoss

Hi @msantoso, this is an interesting problem!

I believe I have got a solution which I have attached.

Essentially I have parsed out each word into an indivdual line, then calculated the length of the word before doing a running total.

I have then used the floor function to acknowledge if each word should be on the 1st, 2nd, 3rd, etc. lines, before using the summerize tool to bring the strings back together.

Ben

NewLine50Chars.yxzp

msantoso

hi Ben,

thanks a lot for your help.

this is exactly doing what I needed.

however, when I saved the output into a csv file, and opened it with Excel, the text did not come into one single cell.

Shouldn't we have a breakline after the last word of each subline?

thank you

myriam

cmcclellan

That also might be how Excel processes CSV files. I'd recommend saving to XLS format and opening in Excel, or saving as CSV and opening in Notepad (or another text editor of your choice)

ponraj

Here is my solution for your case. Steps for formatting excel output as below. Hope this is helpful.

1.Open the excel output

2.Press Ctrl+A to select the output text.

3.Click Home > Format > AutoFit Column Width

4.Click Home>Wrap Text to get the desired output.

Workflow Result ResultExcel output

Split a text into 50-character long strings and a break line.yxmd

HansWolting

Nice solution! Thanks for that.

One small fix I applied: I changed the calculation of Length to Lenght([F1])+1. This will take into account the spaces which are added when concatenating the words afterwards.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703