Discussions

Expand all | Collapse all

Multiple Character delimiter in formula

  • 1.  Multiple Character delimiter in formula

    Posted 17 days ago
    I'm trying to cut out the last 5 digits of a Serial Number if the length is greater than 11, and I'm also trying to add a - after the third digit if the type is set to Master... What I have so far:

    var number AWBLength = Length([Number]);
    var number Cut = 5;
    var text Short = If($AWBLength > 11,(Left(ToText([Number]),$AWBLength - $Cut)),[Number]);
    var text Prefix = If([Type]="Master", Left(ToText([Number]),3),"");
    var text mini = Right($short, $Prefix);
    var text Result = If([Type]= "Master", $prefix&"-"&$mini,$Short);

    This works great except for $mini.

    if it's 1234567890, I want mini to be 4567890, since prefix would be 123.


    ------------------------------
    Ryan Stanford
    ------------------------------


  • 2.  RE: Multiple Character delimiter in formula

    Posted 16 days ago
    With the formula you posted above - it returns the following:

    So looks like it's behaving as expected?  When Type = "Master", the prefix is 123, with mini being 4567890.

    1)  With Number = 1234567890, Type = "Master"
    Result = 123-4567890

    2)  With Number = 1234567890, Type <> "Master"
    Result = 1234567890


    ------------------------------
    Xavier Fan
    Quick Base Solution Provider
    http://xavierfanconsulting.com/
    ------------------------------



  • 3.  RE: Multiple Character delimiter in formula

    Posted 14 days ago
    That example wasn't the best... as if say it's a 34587594513 will not work out as intended, where I want it to be 345-87594513

    ------------------------------
    Ryan Stanford
    ------------------------------



  • 4.  RE: Multiple Character delimiter in formula

    Posted 14 days ago
    Ok - then you'd want to change your mini line as follows - to use "NotLeft", and get the remainder of the text after excluding the leftmost 3 characters.

    var number AWBLength = Length([Number]);
    var number Cut = 5;
    var text Short = If($AWBLength > 11,(Left(ToText([Number]),$AWBLength - $Cut)),[Number]);
    var text Prefix = If([Type]="Master", Left(ToText([Number]),3),"");
    var text mini = NotLeft($short, 3);
    var text Result = If([Type]= "Master", $prefix&"-"&$mini,$Short);


    ------------------------------
    Xavier Fan
    Quick Base Solution Provider
    http://xavierfanconsulting.com/
    ------------------------------



  • 5.  RE: Multiple Character delimiter in formula

    Posted 14 days ago
    As I was awaiting response, I came to the exact same solution.

    Thank you very much, Xavier!

    ------------------------------
    Ryan Stanford
    ------------------------------



  • 6.  RE: Multiple Character delimiter in formula

    Posted 14 days ago
    Ok great!

    ------------------------------
    Xavier Fan
    Quick Base Solution Provider
    http://xavierfanconsulting.com/
    ------------------------------



  • 7.  RE: Multiple Character delimiter in formula

    Posted 14 days ago
    I've done some further research, and another option is to use the split() function.

    ------------------------------
    Ryan Stanford
    ------------------------------