Spliting a long txt string into two parts at a variable mark

[jcrawford08]

Hi all,

Here is the dillemma; I have a report that has an explanation section that sometimes (unfortunately, out of necessity) carries on far longer than others. In the scenario I face today, the character count hits at 6505.

The report is written so the end product is easy to follow and flows well(using keep togethers), sadly, this is causing a full blank page to appear prior to displaying any of the data from the field in question - due to its large size not fitting on the page.

I would like to split the field into two formula's, cutting the characters off at the full paragraph before 4500 characters.

I can get the first formula fine:

if len({myfield})<4500 then{myfield}
else
left({myfield},instrrev(left({myfield},4500.00),chr(13))-1)

But then how do I reference that character position to pull out the 2nd half of the string? I have tried many versions with little success... any ideas?

As always - your help is greatly appreciated!

Not in word only, but in deed also... 1Jn3:18

 

[jcrawford08]

Ideally, I would be able to create a formula that would split the field apart at the last full paragraph prior to 4500 characters, then simply reference it as an array. I'm just not sure how to split it without an established delimiter, but rather at a defined character location...

Then I would just show (myfieldarray)[1] and (myfieldarray)[2] respectively...

But alas - you guys are the experts, let me know what you think...

Not in word only, but in deed also... 1Jn3:18

 

[MCuthill]

jcrawford,

Assuming I understand correctly, if length is over 4,500 characters, you want to split the string so the first 4,500 characters are in Formula A and the remaining in Formula B? It is possible this would ever exceed 9,000 and need to be split in 3 parts?

Assuming you will only ever split into two parts, how about something like the following to retreive the 2nd portion?

 mid({YourField},4501,Len({yourfield})-4500)

From HELP:
Mid(str, start)
Mid(str, start, length)

Arguments
str is the text string from which one or more characters is being extracted.
start is a whole number indicating the position of the first character to extract.
length is a whole number indicating the number of characters to be extracted from the text string. This argument is optional. If a value is not specified, the rest of the string from the starting position is extracted.

Now that I see the length parameter is optional, I think just

Mid({YourField},4501)

should work.

Getting the first portion should be as simple as:

Left({YourField},4500)

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[swiss2007]

Have you tried

Right click on the field -----> Format Text ------> Uncheck Keep Object Together.

 

[jcrawford08]

here's what I ended up with that seemed to work okay. Given the data is in paragraph format, keeping consistency proved tricky, but here's what I've got...

myfieldformula1 (gives data from the last full paragraph of the 1st 4500 characters):

numbervar fieldlen;
fieldlen:=4500;

if len({myfield})<4500 then{myfield}
else left({myfield},
instrrev(left({myfield},fieldlen),chr(13))-1)

Then for myfieldformula2 (displays all additional not displayed in the first formula), I did the following:

"Continued From Previous..."&chr(13)&chr(13)&
right({myfield},
(len({myfield})-len({@myfieldformula2})))

of course, the beginning text would be optional, I included because fields of this size in my report would split between pages.


thanks for all your help!!

Not in word only, but in deed also... 1Jn3:18