Character count issue

[JS27]

Hello community,

Newbie needing some guidance please!

I have (proudly!) built a work-flow which gives the below 'Current Output', pulling data in from a few locations (sample below).

The issue I cannot work out how to solve however, is that I have a character limit of 250 within the 'Text' column, and which point I need to cut the sentence and continue in the next row (replicating the 'Ref' and 'Period' information).

I hope this description is clear. Any help would be greatly appreciated!

Sample current and desired output.

[danrh · Accepted solution]

Try the below:

This will split the line at a determined number of characters, but also make sure you don't divide mid-word.  I'm sure there's a better way to do this, but I had fun doing it this way :)

Basically I'm splitting each word out onto a separate row, counting the characters, creating a running total of that count, and then assigning a line number based on that total.  Concatenate back together based on that line number and voila!  In the attached, I'm assuming you want the split at 30 characters, just change to 250 and you should be good to go.

Hope it helps!

[JS27]

Wow that is really cool, thanks @danrh

Can you perhaps explain the Mod formula a bit more? I can see what it's doing (and where to change my requested value), I just don't know why!

Thanks very much though for the workable solution, I've been toiling for quite some time haha

[danrh]

The Mod formula gives a remainder from a division, with the first variable being what you're dividing and the second being what you're dividing by.  So if you've got Mod(10,3), you'll end up with 1 as the answer (10 / 3 = 3 with a remainder of 1).  So in this case, Mod(RunTot_Characters-1,30) means that it will give the remainder after dividing by 30.  I'm using it to reset the running total every time it breaks the 30 character limit.  I've added the -1 just in case the running total hits 30 exactly --- in that case, we want the word to end up on the previous line, not the next line, as it doesn't break the 30 character limit.

Hopefully that makes sense, let me know if you'd like some more clarification.

[JS27]

Thanks for the reply and apologies for the slow response!

It is much appreciated :)