How do I select all text in a string BEFORE the last space?

705 Views

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