Forum Discussion

RyanStanford1's avatar
RyanStanford1
Qrew Captain
5 years ago

Multiple Character delimiter in formula

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
------------------------------
Formulas and functions

6 Replies

Sort By
  • XavierFan's avatar
    XavierFan
    Qrew Cadet
    5 years 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/
    ------------------------------
    • RyanStanford1's avatar
      RyanStanford1
      Qrew Captain
      5 years 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
      ------------------------------
      • XavierFan's avatar
        XavierFan
        Qrew Cadet
        5 years 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/
        ------------------------------