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. &nbsp;Regardless of the original length, I need to reduce the numbers to a total of 9.&nbsp; The first four are whole numbers and the last 5 are decimals.
&nbsp;The desired out-come:
&nbsp;2.18 = 000218000
46.71428571 = 004671428
163.5 = 016350000
10.9 = 001090000
&nbsp;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.&nbsp; Again, I'm not sure automating via a formula is possible but wanted to pick everyone's respective brains.
&nbsp;------------------------------Jamie Strauss------------------------------

markshnier__you · Answer

Nothing like a little brainteaser in the morning.

try this

var number Input = [Raw Number];&nbsp; // insert your field name here
var number Integer = Int($Input);
var number Decimal = $Input - $Integer;
&nbsp;
var text FirstFour = PadLeft(ToText($Integer),4,"0000");
&nbsp;
var text LastFive = PadRight(Left(SearchAndReplace(ToText($Decimal),"0.",""),5),5,"00000");
&nbsp;
$FirstFour &amp; $LastFive------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

jamiestrauss · Answer

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

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

jamiestrauss · Answer

Mark! Worked like a charm!! Thank you so much! Now, any ideas on how to export it so excel doesn't drop all of the zeros?------------------------------Jamie Strauss------------------------------

markshnier__you · Answer

I don't know a way to have the Excel CSV open up and maintain the leading zeros.&nbsp; The only way I caan think of would be to change the last line to this

"#" &amp; $FirstFour &amp; $LastFive

Then Excel would see that as a text string.&nbsp; Any non numeric value in the string will cause Excel to treat it as text - there is nothing special about using an #.------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

Forum Discussion

Formula modifying an existing set up numbers

6 Replies