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

[Former Member]

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

[DellSC]

There actually might be an easier way to do this:

{@First}

NumberVar nAt := Length({VENDOR.NAME});

StringVar result := {VENDOR.NAME};

While (nAt > 40) do

{

   nAt := InStrRev(result, " ");

   result := left(result, nAt);

}

result

{@Second}

LTrim(Replace({VENDOR.NAME}, {@First}, ""))

-Dell

[Former Member]

Thanks Dell!

I tried copying your syntax but could not save {@First}

NumberVar nAt := Length({VENDOR.NAME});

StringVar result := {VENDOR.NAME};

While (nAt > 40) do

{

   nAt := InStrRev(result, " ");

   result := left(result, nAt);

}

result

Error: The matching } for this field name is missing.

I tried

NumberVar nAt := Length({VENDOR.NAME});

StringVar result := {VENDOR.NAME};

If  (nAt > 40) then

(

   nAt := InStrRev(result, " ");

   result := left(result, nAt);

else

result

When I did this I could run the report but don't get the correct results, I get:

International Department of Labour and Internal

Relations

When I need to get:

International Department of Labour and

Internal Relations

I hope I am explaining my problem correctly!

This is all in relation to a Positive Pay file for the bank where they will not accept a Payee Name longer than 40 characters and the data will allow for 60 characters. So need to split the name into 2 fields if longer than 40 in order to display on the cheque as it will show in the file to the bank.

[abhilash_kumar]

Hi Denise,

Try this code:

local stringvar s := {VENDOR.NAME}; 
local stringvar array arr := split(s," "); 
local numbervar i; 
numbervar lcount; 
lcount := 1; 
local numbervar fixed := 40; 
local booleanvar nl := false; 
for i := 1 to ubound(arr) do 
( 
    If i = 1 then 
    ( 
        local stringvar nstring := nstring + trim(arr[i]) + " "; 
        local numbervar rem := fixed - len(nstring); 
    ) 
    else if i <> 1 and i <> ubound(arr) then 
    ( 

        if len(arr[i]) <= rem then 
        ( 
            nstring := nstring + trim(arr[i]) + " "; 
            rem := (fixed*lcount) - (len(nstring)+lcount);        
            stringvar s := nstring; 
            numbervar t := rem;  
        ) 
        else 
        ( 
            nstring := rtrim(nstring) + chr(13) + trim(arr[i]) + " "; 
            lcount := lcount + 1; 
            nl := true; 
            rem := (fixed)- len(arr[i] + " "); 
        )         
    ) 
    else if i = ubound(arr) then 
    ( 
        if len(arr[i]) <= rem then 
        ( 
            nstring := nstring + trim(arr[i]); 
            rem := fixed - len(nstring); 
        ) 
        else 
        ( 
            nstring := nstring + chr(13) + trim(arr[i]); 
            rem := fixed - len(arr[i] + " "); 
        ) 
    ) 
); 
nstring;

Drag and drop the formula field > Right-click it > Format Field > Common tab > Check 'Can Grow'.

I hope this helps.

-Abhilash

[Former Member]

This is almost perfect Abhilash!!!! Thank you.

However, I think my reply to Dell might have been a bit misleading.

I can't have the text that is >40 which is after the last space on the second line (can grow) of the field, it needs to be in a separate field...

Field # 1 - "International Department of Labour and"

Field #2 - "Internal Relations"

So I need two fields with 2 formulas....I had the second field/formula figured out but maybe not as efficient as it could be, but Field #1 was my problem. Can your formula be tweaked to just get the text before the last space???

Thanks again in advance, this is very helpful

Denise

[abhilash_kumar]

Absolutely.

Create a formula called @Field1 with this code:

stringvar field1 := split({@Formula Name},chr(13))[1];

Replace 'Formula Name' in the code above with the actual name of the formula you used for the code I posted in my previous reply.

For the second field, create another formula with this code:

stringvar field2 := split({@Formula Name},chr(13))[2];

I hope this helps.

-Abhilash

[former_member11696]

Hi Denise,

For Dell's suggestion the @first and @Second is the formula names that you needed to create.

Don