Hi,
I have a text field (60 characters max) that needs to be split into two fields 40 char and 20 char. The data is a Vendor Name and therefore can't just be trimmed at 40 since that might cause a word to be cut off.
I can get the last full word of the name where the name is over 40 characters, but I need the full text before the last space in the 40 Character field
Example:
International Department of Labour and Internal Relations
Field 1 should read: International Department of Labour and
Field 2 should read: Internal Relations
How can I extract the data for Field 1
This is how I got Field 2:
Formula 1 -
left ({VENDOR.NAME},40)
Result : "International Department of Labour and I"
Formula 2 -
if len({VENDOR.NAME})>40 then
if instr({@Form 1}," ") > 0 then
split({@Form 1}," ")[ubound(split({@Form 1}," "))] else
{@Form 1}
else ''
Result: "I"
Formula 3
Mid ({VENDOR.NAME},41 )
Result "nternal Relations"
Formula 4
if {@form 2} = '' then '' else
{@Form 3}+{@form 2}
Result: "Internal Relations"
Thanks in Advance,
Denise