Forum Discussion

JamieStrauss's avatar
JamieStrauss
Qrew Member
11 months ago

Formula modifying an existing set up numbers

Hi Everyone, I have a formula I am trying to find but I'm losing hope. I need to remove a decimal in a string of numbers and reformat in a specific way.  Regardless of the original length, I need to reduce the numbers to a total of 9.  The first four are whole numbers and the last 5 are decimals.

 

The desired out-come:

 

2.18 = 000218000

46.71428571 = 004671428

163.5 = 016350000

10.9 = 001090000

 

I currently have a team manually updating 812 records which is not efficient at all. There has got to be a better way, I just can't figure it out.  Again, I'm not sure automating via a formula is possible but wanted to pick everyone's respective brains.

 



------------------------------
Jamie Strauss
------------------------------

6 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    11 months ago

    Nothing like a little brainteaser in the morning.

    try this

    var number Input = [Raw Number];  // insert your field name here
    var number Integer = Int($Input);
    var number Decimal = $Input - $Integer;
     
    var text FirstFour = PadLeft(ToText($Integer),4,"0000");
     
    var text LastFive = PadRight(Left(SearchAndReplace(ToText($Decimal),"0.",""),5),5,"00000");
     
    $FirstFour & $LastFive


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • JamieStrauss's avatar
      JamieStrauss
      Qrew Member
      11 months ago

      THANK YOU! It's ALMOST working! But right at the end, $FirstFour & $LastFive is highlighted saying a number was expected but found text. Maybe I need to change the field type? I have formula numeric



      ------------------------------
      Jamie Strauss
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        11 months ago

        The field type needs to be formula text because it has leading zeros and trailing zeros.



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------