Formula modifying an existing set up numbers

Question

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------------------------------

MarkShnierYou · Answer

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
------------------------------

Sign up

Login to the community